|
### get-all-serverOS-servers-from-AD_then_WMI-info_and_write-to-EXCEL_or_CSV.ps1 ###
##########################################################################################################
################# created by Arthur Sulkowski - Microsoft Systems Engineer - in May 2012 #################
##########################################################################################################
#Get Comprehensive details about a list of servers listed in a text file or directly from AD using multiple WMI queries and write to Excel or CSV, this program:
# 0. requires powershell v3+, and MS Excel unless -CSVoutput parameter is specified
# 1. either gets all computer objects from AD that have operating system matching Server OS (enabled and disabled objects, no searchbase defined so ALL of AD) or all hostnames from a specified serverlist text file
# 2. creates and opens a new excel file, populates column heading names etc or creates a CSV Header if -CSVoutput parameter is specified
# 3. gets AD information about each computer in the server list
# 4. powershell-ping tests each server
# 5. does a powershell version of nslookup against each server
# 6. obtains up to date server environment/tech owner/app owner/business owner details for each server from external csv txt files
# 7. runs multiple WMI queries against each server to obtain detailed information on each server
# 8. records all results to the excel spreadsheet, then saves and closes the excel file or outputs a CSV if -CSVoutput parameter is specified
#NB: for testing and modification, use the -altpath runtime parameter allowing you to specify an alternate save path so as to not overwrite production data on the file system
# *** NOTE: WHEN CUSTOMIZING THIS SCRIPT FOR A NEW ENVIRONMENT *** - look out for the following and make appropriate changes throughout the code:
# CSV FILES : search for "\\server\share\" and replace accordingly
# CURRENT DOMAIN : search for "bottlingcompany.com" and replace accordingly
# DOMAIN NAMES : modify the following function with relevant domain names, search for "function DoScanDomainCheck (){" and replace accordingly
# IP ADDRESS LOCATION : update the IP subnets in the section at the bottom when subnet to location mappings are known - search for "$hostip = $ping.IPV4Address[0].IPAddressToString" and update sections below it accordingly
#--------------------------------------------------------------------------------------------------------#
Param ($serverlist, `
$scandomain, `
$excludethese, `
$altpath, `
[switch]$invisible, `
[switch]$namingconv, `
[switch]$skipWMI, `
[switch]$skipPING, `
[switch]$allADcomputers, `
[switch]$nodomain, `
[switch]$CSVoutput, `
[switch]$help)
$RunMeHelpOption = @'
#######################################################################################
Run this program with '-help' switch parameter to display all options and additional functionality.
Display Help:
.\get-all-serverOS-servers-from-AD_then_WMI-info... -help
#######################################################################################
'@
$HelpText = @'
#######################################################################################
Purpose : This program either retrieves all server-OS computers (servers) from the
current Active Directory domain, or from a target specified domain, or from a
plain text file server list against the current domain, or a target specified
domain, and obtains AD and WMI details about each computer saving the data to
an excel file.
Usage:
.\get-all-serverOS-servers-from-AD_then_WMI-info... [-serverlist ] [-scandomain ] [-help]
Where:
-serverlist Specifies a text file containing a list of servers to
process. If this parameter is omitted, a server list
will be obtained from Active Directory.
-scandomain Specifies the target domain to process the list of
servers against. Acceptable values are "current", or the fully
qualified target domain name. If this parameter is omitted, the
current domain will be used.
NOTE: Ensure this program is run in a powershell window
with the appropriate domain admin credentials for the
target domain.
-excludethese Allows the script to exclude computers from the scan list.
An individual server, or comma separated server list, or a
UNC path to a single-column text file can be specified.
-altpath Allows redirecting the script to save the results to an
alternate path.
-invisible Makes the excel window remain hidden while the script runs.
-namingconv Uses an alternative naming convention AD search. Retrieves all
server OS computer names starting with a* or bcoau*, hardcoded for BCO.
-skipWMI Will skip WMI checks, use when WMI attempts freeze up progress.
-skipPING Will skip performing a PING check before attempting a WMI query.
-allADcomputers Will get all AD computers regardless of their Operating System.
-nodomain If Active Directory Web Services are not running, and the get-ad
cmdlets are not available, use this to omit any AD searching.
NOTE: The -serverlist parameter is then implied and must be used.
Use the following dsquery command in a powershell prompt running
as the appropriate domain credentials for the target domain to
obtain a serverlist from AD, then run this program again with the
-serverlist and -nodomain parameters:
'dsquery * domainroot -filter "(&(objectCategory=computer)(operatingSystem=Windows Server*))" -limit 10000 | %{$_.split(",")[0]}| %{$_.split("=")[1]} | out-file c:\temp\serverlist.txt'
-CSVoutput Useful if Excel is not available, outputs to CSV.
-help Displays this help.
Examples:
.\get-all-serverOS-servers-from-AD_then_WMI-info... -scandomain mydomain.local
.\get-all-serverOS-servers-from-AD_then_WMI-info... -serverlist c:\serverlist.txt
.\get-all-serverOS-servers-from-AD_then_WMI-info... -serverlist c:\serverlist.txt -scandomain mydomain.local
.\get-all-serverOS-servers-from-AD_then_WMI-info... -serverlist c:\serverlist.txt -scandomain mydomain.local -altpath c:\temp
.\get-all-serverOS-servers-from-AD_then_WMI-info... -serverlist c:\serverlist.txt -excludethese server1,server2,server3
.\get-all-serverOS-servers-from-AD_then_WMI-info... -serverlist c:\serverlist.txt -excludethese c:\temp\mylist.txt
#######################################################################################
'@
###GLOBAL VARIABLE DECLARATIONS
$global:thisexcel = ""
#default $erroractionpreference is "Continue"
$erroractionpreference = "SilentlyContinue"
###END GLOBAL VARIABLE DECLARATIONS
###ALL FUNCTIONS LISTED HERE
function DoScanDomainCheck (){
param ($scandomain)
write-host "Validating '$scandomain' domain..." -back black -fore yellow
switch ($scandomain.tolower()){
current {$scandomain = (get-addomain | select DNSRoot).dnsroot; $scandomain = $scandomain.tolower(); break}
ausdom {$scandomain = "bottlingcompany.com"; break}
bottlingcompany.com {$scandomain = "bottlingcompany.com"; break}
default {write-host "None of the hard-coded domain names selected. Validating..." -back black -fore yellow; break}
# default {write-host "ERROR: something went wrong, invalid target domain. Exiting..." -back black -fore red; exit}
}
write-host "Returning validated domain '$scandomain'..." -back black -fore yellow
return $scandomain
}
function determinehost (){
param ($server)
#script to get the hyper-v host's name from a remote hyper-v vm's registry
#values for HKLM registry provider
$HKLM = 2147483650
#connect to remote WMI namespace
$reg = Get-WmiObject -List -Namespace root\default -ComputerName $server | Where-Object {$_.Name -eq "StdRegProv"}
#obtain registry value
$HVHname = $reg.GetStringValue($HKLM,"SOFTWARE\Microsoft\Virtual Machine\Guest\Parameters" ,"HostName").svalue
#return guest's host name
return $HVHname
}
###END ALL FUNCTIONS
###TEST COMMAND LINE PARAMETERS
#if "-help" cmd line parameter was entered, or the "-serverlist " parameter was not entered, display the help text to standard output (cmd prompt)
if ($help){
#help parameter detected, print help text and exit
write-host $HelpText
exit
}
#help parameter was not specified so continue, but print that a help menu is available via the -help switch
write-host $RunMeHelpOption
#if "-altpath" cmd line parameter was entered, prompt user to specify target save location
if ($altpath){
$counter = 1
if(!($altpath.EndsWith("\"))){$altpath = $altpath + "\"}
while(!(test-path $altpath)){
$altpath = read-host "invalid path. $counter of 3 failed attempts. please specify alternate save path"
if($altpath -eq ""){$altpath = "invalid"}
if(!($altpath.EndsWith("\"))){$altpath = $altpath + "\"}
$counter++
if($counter -gt 2){exit}
}
#store the path in $savepath simply for outputting the save directory in a write-host cmd at the end of this script
$savepath = $altpath.substring(0,$altpath.Length-1)
}
#Get Start Time and store it in the $startDTM variable
$startDTM = (Get-Date)
if($CSVoutput){
write-host "`nStarting this program at $startDTM `nA CSV file will be output on execution completion.`n"
} else {
write-host "`nStarting this program at $startDTM `nPlease note, MS Excel must be installed for this program to function. Use -CSVoutput if Excel is not available.`n"
}
if($serverlist){
#if serverlist parameter is specified, test connection to the text file to ensure it is valid
if (!(test-path $serverlist)){
write-host "ERROR: `"$serverlist`" is not a valid file" -back black -fore red
write-host "REQUIRED ACTION: Re-run this program using a valid filename" -back red -fore white
exit
}
$serverlistmethod = "2"
write-host "Proceeding with Text File server list method" -back black -fore green
} else {
#serverlist not specified so domain method will be used
$serverlistmethod = "1"
if(!($nodomain)){write-host "Proceeding with Active Directory server list method" -back black -fore green}
}
#regardless of whether serverlist or AD method will be used, check if scandomain value specified as domain to scan can be defined for both text file list and AD methods
if(!($nodomain)){
#import activedirectory module for powershell v2 and earlier
import-module activedirectory
if(!($scandomain)){
#scandomain parameter not specified so run against current domain
$scandomain = "current"
#scandomain has been specified as one of the acceptable values, set the FQDN variable via a function call
$scandomain = DoScanDomainCheck $scandomain
if($scandomain -ne "current"){
if($thisdomain = get-addomain $scandomain | select DNSRoot,DistinguishedName){
write-host "Proceeding with current Active Directory domain '$scandomain'. Obtaining DNS Root and DistinguishedName..." -back black -fore green
$global:thisdomainROOT = $thisdomain.dnsroot
$global:thisdomainDN = $thisdomain.DistinguishedName
} else {
write-host "ERROR: a valid and accessible domain has not been entered, ensure Active Directory web services are running on at least one domain controller in the target domain, if this is not possible, use the following dsquery command in a separate powershell prompt running as the appropriate domain credentials to obtain a serverlist from AD for that domain and run this program again with the -serverlist and -nodomain parameters" -back black -fore yellow
write-host "'dsquery * domainroot -filter `"(&(objectCategory=computer)(operatingSystem=Windows Server*))`" -limit 10000 | %{`$_.split(`",`")[0]} | %{`$_.split(`"=`")[1]} | out-file c:\temp\serverlist.txt'`n" -back black -fore cyan
write-host "Exiting..." -back black -fore red
exit
}
} else {
#scandomain was not updated in the DoScanDomainCheck function which means the get-addomain cmdlet call failed
write-host "ERROR: a valid and accessible domain has not been entered, ensure Active Directory web services are running on at least one domain controller in the target domain, if this is not possible, use the following dsquery command in a separate powershell prompt running as the appropriate domain credentials to obtain a serverlist from AD for that domain and run this program again with the -serverlist and -nodomain parameters" -back black -fore yellow
write-host "'dsquery * domainroot -filter `"(&(objectCategory=computer)(operatingSystem=Windows Server*))`" -limit 10000 | %{`$_.split(`",`")[0]} | %{`$_.split(`"=`")[1]} | out-file c:\temp\serverlist.txt'`n" -back black -fore cyan
write-host "Exiting..." -back black -fore red
exit
}
} else {
#scandomain parameter is specified as something else, validate the domain name
$quitthis = 0
while ($true){
$scandomain = DoScanDomainCheck $scandomain
if(get-addomain $scandomain){break}
write-host "`nWARNING: a valid and accessible domain has not been entered, ensure Active Directory web services are running on at least one domain controller in the target domain, if this is not possible, use the following dsquery command in a separate powershell prompt running as the appropriate domain credentials to obtain a serverlist from AD for that domain and run this program again with the -serverlist parameter" -back black -fore yellow
write-host "'dsquery * domainroot -filter `"(&(objectCategory=computer)(operatingSystem=Windows Server*))`" -limit 10000 | %{`$_.split(`",`")[0]} | %{`$_.split(`"=`")[1]} | out-file c:\temp\serverlist.txt'`n" -back black -fore cyan
$quitthis++
if($quitthis -eq 3){write-host "ERROR: a valid and accessible domain has not been entered, please try running this program again and use the '-help' switch`n" -back black -fore red; exit}
$scandomain = read-host "Invalid domain specified. Please choose and specify valid domain name"
}
write-host "Proceeding with '$scandomain' Active Directory domain. Obtaining DNS Root and DistinguishedName..." -back black -fore green
$thisdomain = get-addomain $scandomain | select DNSRoot,DistinguishedName
$global:thisdomainROOT = $thisdomain.dnsroot
$global:thisdomainDN = $thisdomain.DistinguishedName
}
} else {
write-host "Skipping all Active Directory checks and proceeding only with WMI queries against the specified serverlist text file." -back black -fore green
if ($serverlistmethod -eq "1"){
#if the -serverlist parameter was not specified but the -nodomain parameter was, then prompt the user to specify a serverlist text file as its now a requirement
while($true){
write-host "`nERROR: A valid serverlist text file has not been specified and is required when the -nodomain parameter is used. `nUse the following dsquery command in a separate powershell prompt running as the appropriate domain credentials to obtain a serverlist from AD for that domain, then specify the textfile path here." -back black -fore yellow
write-host "'dsquery * domainroot -filter `"(&(objectCategory=computer)(operatingSystem=Windows Server*))`" -limit 10000 | %{`$_.split(`",`")[0]} | %{`$_.split(`"=`")[1]} | out-file c:\temp\serverlist.txt'`n" -back black -fore cyan
$serverlist = read-host "Please specify the full path to the serverlist text file [C:\temp\serverlist.txt]"
if(!($serverlist)){$serverlist = "C:\temp\serverlist.txt"}
if (test-path $serverlist){break}
}
$serverlistmethod = "2"
write-host "Proceeding with Text File server list method" -back black -fore green
} else {
write-host "`nA valid serverlist text file has been found, progressing..." -back black -fore green
}
}
###END TEST COMMAND LINE PARAMETERS
<# CSV's if not available should be excluded
###MODIFY-ABLE VARIABLES - 7 required CSV files including server-environment information & environment-owner information (all 5 are 1:1 2-column CSVs), and 1 optional TXT file for server list input rather than using AD:
$environmentownerCSV = "\\server\share\AD SolarWinds and WMI server audits\for powershell\environment-owners.csv"
$serverbackreqdCSV = "\\server\share\AD SolarWinds and WMI server audits\for powershell\server-backup-requirement.csv"
$servercommvaultCSV = "\\server\share\AD SolarWinds and WMI server audits\for powershell\server-commvault-status.csv"
$serverenvironmentCSV = "\\server\share\AD SolarWinds and WMI server audits\for powershell\server-environments.csv"
$serverproductionCSV = "\\server\share\AD SolarWinds and WMI server audits\for powershell\server-production-status.csv"
$serverwarrantyCSV = "\\server\share\AD SolarWinds and WMI server audits\for powershell\server-warranty-date.csv"
$extendedwarrantyCSV = "\\server\share\AD SolarWinds and WMI server audits\for powershell\extended-warranty-date.csv"
###END MODIFY-ABLE VARIABLES
#>
# at this point command line parameters have been identified, server list method defined, target domain defined, server environment owners read, next - obtain the server list and process all servers
#--------------------------------------------------------------------------------------------------------#
#OBTAIN LIST OF SERVERS USING DEFINED SERVER LIST METHOD (AD or TEXT FILE LIST)
#get a full upto date list of all "server OS" servers directly from AD and store to $colComputers variable
if ($serverlistmethod -eq "1"){
if($scandomain -eq "bottlingcompany.com"){
#if AD method, for specific domain:
if($namingconv){
write-host "`nGetting an upto date list of all A* & BCOAU* naming convention computers from ActiveDirectory with a Server OS or blank OS... (allow about a minute to complete)"
$allServers = Get-ADComputer -filter {(name -like "A*" -or name -like "BCOAU*") -and (OperatingSystem -notlike "*" -or OperatingSystem -notlike "Linux*" -and OperatingSystem -notlike "Cent*" -and OperatingSystem -notlike "Windows 10*" -and OperatingSystem -notlike "Windows 8*" -and OperatingSystem -notlike "Windows 7*" -and OperatingSystem -notlike "Windows XP*")} -properties CanonicalName, Created, IPv4Address, Modified, OperatingSystem, OperatingSystemServicePack, passwordlastset, description -server $scandomain
} elseif($allADcomputers){
write-host "`nGetting an upto date list of all computers from ActiveDirectory... (allow about a minute to complete)"
$allServers = Get-ADComputer -filter * -properties CanonicalName, Created, IPv4Address, Modified, OperatingSystem, OperatingSystemServicePack, passwordlastset, description -server $scandomain
} else {
write-host "`nGetting an upto date list of all Server OS computers from ActiveDirectory... (allow about a minute to complete)"
$allServers = Get-ADComputer -filter {OperatingSystem -like "Windows*Server*" -or OperatingSystem -eq "Windows 2000 Server"} -properties CanonicalName, Created, IPv4Address, Modified, OperatingSystem, OperatingSystemServicePack, passwordlastset, description -server $scandomain
}
} else {
#if AD method, for specified domain:
write-host "`nThis program needs to either be run using a powershell prompt running under $global:thisdomainROOT domain admin credentials in the current environment, or directly run in the $global:thisdomainROOT environment using $global:thisdomainROOT domain admin credentials.`n"
if($allADcomputers){
write-host "`nGetting an upto date list of all computers from the $global:thisdomainROOT domain ActiveDirectory... (allow about a minute to complete)"
$allServers = Get-ADComputer -filter * -properties CanonicalName, Created, IPv4Address, Modified, OperatingSystem, OperatingSystemServicePack, passwordlastset, description -SearchBase $global:thisdomainDN -server $global:thisdomainROOT
} else {
write-host "`nGetting an upto date list of all Server OS computers from the $global:thisdomainROOT domain ActiveDirectory... (allow about a minute to complete)"
$allServers = Get-ADComputer -filter {OperatingSystem -like "Windows*Server*" -or OperatingSystem -eq "Windows 2000 Server"} -properties CanonicalName, Created, IPv4Address, Modified, OperatingSystem, OperatingSystemServicePack, passwordlastset, description -SearchBase $global:thisdomainDN -server $global:thisdomainROOT
}
}
#store all server hostnames to $colComputers variable
$colComputers = $allServers.name
write-host "done"
} elseif ($serverlistmethod -eq "2"){
# Text file server list method
$colComputers = gc $serverlist | Select-Object -Unique
}
#sort the list of computer hostnames
$colComputers = $colComputers | sort-object
if($serverlistmethod -eq "1"){$allServers = $allServers | sort-object}
#if the excludethese cmd line parameter has been entered, remove any matching servers from the obtained server list
if($excludethese){
$temparray = $colComputers
$temparray = $temparray | %{$_.toupper()}
$colComputers = @()
if($excludethese.gettype().name -eq "string"){
#if its a string, then a single computer name has been specified (or a path location)
if($excludethese.contains(".")){
$hasdot = $true
#if has a dot, implies its a filename, so test this
if(test-path $excludethese){
write-host "excluded list text file successfully tested, attempting to retrieve list of servers to exclude from this text file" -back black -fore yellow
$excludethese = gc $excludethese
$excludethese = $excludethese | %{$_.toupper()}
$temparray | %{if(!($excludethese.contains($_))){$colComputers += $_}}
$testpath = $true
} else {
#else, assume the dot is meant to be part of a computer name for a single specified computer name
write-host "excluded list contains a dot suggesting a filename, however the path is invalid, attempting to use the specified as a single exclusion name" -back black -fore red
$excludethese = $excludethese.toupper()
$temparray | %{if($_ -ne $excludethese){$colComputers += $_}}
$testpath = $false
}
} else {
$excludethese = $excludethese.toupper()
$temparray | %{if($_ -ne $excludethese){$colComputers += $_}}
}
} elseif($excludethese.gettype().name -eq "object[]"){
#if its an array, then a comma separated list has been specified
$excludethese = $excludethese | %{$_.toupper()}
$temparray | %{if(!($excludethese.contains($_))){$colComputers += $_}}
}
if($colComputers.count -ne $temparray.count){
$excluded = $temparray.count - $colComputers.count
if($excluded -eq "1"){
write-host "`n$excluded server has been excluded." -fore yellow -back black
} else {
write-host "`n$excluded servers have been excluded." -fore yellow -back black
}
} else {
if($hasdot){
if($testpath){
write-host "`nexcluded list has failed analysis. no servers have been excluded. please check the path and try again." -back black -fore red
} else {
write-host "`nno servers have been excluded list potentially because the excluded list do not exist in the target server list." -back black -fore red
}
}
}
}
write-host "`nServer list obtained ("$colComputers.count" servers )." -fore yellow -back black
#--------------------------------------------------------------------------------------------------------#
#NEXT, for LC domain only, read in owner CSV files - these only apply to LC since they are only known for LC servers and application-environments
if($scandomain -eq "lc.local"){
write-host "`nReading in server environment CSV files..."
#NEXT SECTION reads in the required CSV files that are saved from a master Environment Owners Excel file to populate Environment, Tech/APP/Business Owners, Backup status and requirement, production level, for each server
#--------------------------------------------------------------------------------------------------------#
#read in and convert the environment to owners mapping details (do NOT use import-csv as it behaves differently to what's required here)
write-host "`n - getting environment-owners information from:`n" $environmentownerCSV
$environmentownertext = get-content $environmentownerCSV
$environmentowner = @{}
#split each line in two based on the first comma encountered and populate the hash table declared just above here
foreach($combo in $environmentownertext){
$combo1 = $combo.substring(0,$combo.indexof(","))
$combo2 = $combo.substring($combo.indexof(",")+1)
$environmentowner.add($combo1,$combo2)
}
#--------------------------------------------------------------------------------------------------------#
#read in and convert the is server backup required details (do NOT use import-csv as it behaves differently to what's required here)
write-host "`n - getting server-backup-requirement information from:`n" $serverbackreqdCSV
$serverbackreqdtext = get-content $serverbackreqdCSV
$serverbackreqd = @{}
#split each line in two based on the first comma encountered and populate the hash table declared just above here
foreach($combo in $serverbackreqdtext){
$combo1 = $combo.substring(0,$combo.indexof(","))
$combo2 = $combo.substring($combo.indexof(",")+1)
$serverbackreqd.add($combo1,$combo2)
}
#--------------------------------------------------------------------------------------------------------#
#read in and convert the server to environment mapping details (do NOT use import-csv as it behaves differently to what's required here)
write-host "`n - getting server-commvault-status information from:`n" $servercommvaultCSV
$servercommvaulttext = get-content $servercommvaultCSV
$servercommvault = @{}
#split each line in two based on the first comma encountered and populate the hash table declared just above here
foreach($combo in $servercommvaulttext){
$combo1 = $combo.substring(0,$combo.indexof(","))
$combo2 = $combo.substring($combo.indexof(",")+1)
$servercommvault.add($combo1,$combo2)
}
#--------------------------------------------------------------------------------------------------------#
#read in and convert the server to environment mapping details (do NOT use import-csv as it behaves differently to what's required here)
write-host "`n - getting server-environment information from:`n" $serverenvironmentCSV
$serverenvironmenttext = get-content $serverenvironmentCSV
$serverenvironment = @{}
#split each line in two based on the first comma encountered and populate the hash table declared just above here
foreach($combo in $serverenvironmenttext){
$combo1 = $combo.substring(0,$combo.indexof(","))
$combo2 = $combo.substring($combo.indexof(",")+1)
$serverenvironment.add($combo1,$combo2)
}
#--------------------------------------------------------------------------------------------------------#
#read in and convert the environment to owners mapping details (do NOT use import-csv as it behaves differently to what's required here)
write-host "`n - getting server-production-status information from:`n" $serverproductionCSV
$serverproductiontext = get-content $serverproductionCSV
$serverproduction = @{}
#split each line in two based on the first comma encountered and populate the hash table declared just above here
foreach($combo in $serverproductiontext){
$combo1 = $combo.substring(0,$combo.indexof(","))
$combo2 = $combo.substring($combo.indexof(",")+1)
$serverproduction.add($combo1,$combo2)
}
#--------------------------------------------------------------------------------------------------------#
#read in and convert the server serial number to manufacturer warranty mapping details (do NOT use import-csv as it behaves differently to what's required here)
write-host "`n - getting server-warranty-status information from:`n" $serverwarrantyCSV
$serverwarrantytext = get-content $serverwarrantyCSV
$serverwarranty = @{}
#split each line in two based on the first comma encountered and populate the hash table declared just above here
foreach($combo in $serverwarrantytext){
$combo1 = $combo.substring(0,$combo.indexof(","))
$combo2 = $combo.substring($combo.indexof(",")+1)
$serverwarranty.add($combo1,$combo2)
}
#--------------------------------------------------------------------------------------------------------#
#read in and convert the server serial number to extended interactive warranty mapping details (do NOT use import-csv as it behaves differently to what's required here)
write-host "`n - getting server-warranty-status information from:`n" $extendedwarrantyCSV
$extendedwarrantytext = get-content $extendedwarrantyCSV
$extendedwarranty = @{}
#split each line in two based on the first comma encountered and populate the hash table declared just above here
foreach($combo in $extendedwarrantytext){
$combo1 = $combo.substring(0,$combo.indexof(","))
$combo2 = $combo.substring($combo.indexof(",")+1)
$extendedwarranty.add($combo1,$combo2)
}
#--------------------------------------------------------------------------------------------------------#
write-host "`nEnvironment documentation read completed, progressing onto populating Excel..."
}
#--------------------------------------------------------------------------------------------------------#
#NEXT SECTION creates a new excel object for populating with results, to allow multiple runs of this program against each of the acceptable domains, the excel object handle reference is customized per domain
#create an array of the first 104 columns in Excel for allocating to columns created by this program, simplifying the process of adding new columns by simply incrementing a pointer in this array when creating new columns (rather than modifying this code in a 1000 places)
#NB: the first item, [0], is null so that the firt pointer [1] returns column "A" and is equal to row "1", that way eliminating the need for two counters for rows and columns
$excelcolumns = ("","A","B","C","D","E","F","G","H","I","J","K","L","M","N","O","P","Q","R","S","T","U","V","W","X","Y","Z","AA","AB","AC","AD","AE","AF","AG","AG","AI","AJ","AK","AL","AM","AN","AO","AP","AQ","AR","AS","AT","AU","AV","AW","AX","AY","AZ","BA","BB","BC","BD","BE","BF","BG","BH","BI","BJ","BK","BL","BM","BN","BO","BP","BQ","BR","BS","BT","BU","BV","BW","BX","BY","BZ","CA","CB","CC","CD","CE","CF","CG","CH","CI","CJ","CK","CL","CM","CN","CO","CP","CQ","CR","CS","CT","CU","CV","CW","CX","CY","CZ")
#prepare the excel column and row array pointer (see above) by setting them to 1, then add a column, increment both counters, and repeat until all columns are added
$excelcounter = 1
#move the excel Row counter onto the 2nd row by creating a new variable $intRow with "2" value and commencing the excel row population with this counter
$intRow = 2
#--------------------------------------------------------------------------------------------------------#
#open Excel.exe instance, reference it via $thisexcelobj variable, set the application window to visible and add a workbook (additional excel objects allow for simultaneous run on same computer in multiple powershell windows)
#obtain the current list of all excel.exe processes' ID's
if(!$CSVoutput){
$currex = Get-Process -name Excel
if($scandomain -eq "bottlingcompany.com"){
$thisexcelobj = New-Object -comobject Excel.Application
#the following either keeps excel hidden from screen or visible, its safer to keep it hidden to avoid accidental clicks which require the program to be restarted
if($invisible){
$thisexcelobj.visible = $False
} else {
$thisexcelobj.visible = $True
}
$excelbook = $thisexcelobj.Workbooks.Add()
} else {
$myotherexcelobj = New-Object -comobject Excel.Application
#the following either keeps excel hidden from screen or visible, its safer to keep it hidden to avoid accidental clicks which require the program to be restarted
if($invisible){
$myotherexcelobj.visible = $False
} else {
$myotherexcelobj.visible = $True
}
$excelbook = $myotherexcelobj.Workbooks.Add()
}
#straight away obtain a new updated list of all excel.exe processes
$currex2 = Get-Process -name Excel
#compare the difference based on the ID property to obtain the PID of the excel.exe just started
$thisexcel = Compare-Object $currex.id $currex2.id
if(!$thisexcel){
#if an excel instance did not exist at runtime, assign the current excel PID
$global:thisexcel = (get-process -name excel | select id).id
} else {
#if an excel instance did exist at runtime extract the difference from the compare-object operation and assign the resulting PID to the global variable
$global:thisexcel = $thisexcel.inputobject
}
write-host "This excel PID is"$global:thisexcel
#set workbook settings and current worksheet
$excelbook.CheckCompatibility = $False
$excelbook.DisplayAlerts = $False
$excelsheet = $excelbook.Worksheets.Item(1)
#modify color attributes for the entire first row of the excel spreadsheet
$excelsheet.Cells.Range("1:1").Interior.ColorIndex = 19
$excelsheet.Cells.Range("1:1").Font.ColorIndex = 11
$excelsheet.Cells.Range("1:1").Font.Bold = $True
#freeze the first row
$excelsheet.application.activewindow.splitrow = 1
$excelsheet.application.activewindow.freezepanes = $true
}
#name the first row of cells with headers, referencing the $excelsheet worksheet, and create a new column variable with the column value, both should match each other
$machinenamecell = get-date -Format dd-MM-yy
$machinenamecell += " Hosts"
# create a CSV header row same as the excel header row
$CSVheader = ""
###IF MODIFYING THIS SCRIPT BY ADDING OR REMOVING COLUMNS TO EXCEL, THIS IS THE PLACE TO DO IT; ALL LINES COME IN FIVE's - one for the column number value variable, one for the column letter value, one for adding a header to excel for that column in the first row, one for adding a header to the CSV array, and the last for incrementing the array counter/pointer.
$MachineName = $excelcounter
$MachineNameCOL = $excelcolumns[$excelcounter]
if(!$CSVoutput){$excelsheet.Cells.Item(1,$MachineName) = $machinenamecell}
$CSVheader += $machinenamecell
$excelcounter++
$nslookupIPnslookup = $excelcounter
$nslookupIPnslookupCOL = $excelcolumns[$excelcounter]
if(!$CSVoutput){$excelsheet.Cells.Item(1,$nslookupIPnslookup) = "nslookup-IP nslookup"}
$CSVheader += "`tnslookup-IP nslookup"
$excelcounter++
$checkWMINameagainstADDNS = $excelcounter
$checkWMINameagainstADDNSCOL = $excelcolumns[$excelcounter]
if(!$CSVoutput){$excelsheet.Cells.Item(1,$checkWMINameagainstADDNS) = "check hostname"}
$CSVheader += "`tcheck hostname"
$excelcounter++
$pingtestIP = $excelcounter
$pingtestIPCOL = $excelcolumns[$excelcounter]
if(!$CSVoutput){$excelsheet.Cells.Item(1,$pingtestIP) = "ping test IP"}
$CSVheader += "`tping test IP"
$excelcounter++
$Location = $excelcounter
$LocationCOL = $excelcolumns[$excelcounter]
if(!$CSVoutput){$excelsheet.Cells.Item(1,$Location) = "Location"}
$CSVheader += "`tLocation"
$excelcounter++
$Uptime = $excelcounter
$UptimeCOL = $excelcolumns[$excelcounter]
if(!$CSVoutput){$excelsheet.Cells.Item(1,$Uptime) = "UpTime"}
$CSVheader += "`tUpTime"
$excelcounter++
$RebootState = $excelcounter
$RebootStateCOL = $excelcolumns[$excelcounter]
if(!$CSVoutput){$excelsheet.Cells.Item(1,$RebootState) = "Reboot State"}
$CSVheader += "`tReboot State"
$excelcounter++
$connectivityresults = $excelcounter
$connectivityresultsCOL = $excelcolumns[$excelcounter]
if(!$CSVoutput){$excelsheet.Cells.Item(1,$connectivityresults) = "connectivity results"}
$CSVheader += "`tconnectivity results"
$excelcounter++
$checkpingIPagainstAD = $excelcounter
$checkpingIPagainstADCOL = $excelcolumns[$excelcounter]
if(!$CSVoutput){$excelsheet.Cells.Item(1,$checkpingIPagainstAD) = "check ping IP against AD"}
$CSVheader += "`tcheck ping IP against AD"
$excelcounter++
$hypervhost = $excelcounter
$hypervhostCOL = $excelcolumns[$excelcounter]
if(!$CSVoutput){$excelsheet.Cells.Item(1,$hypervhost) = "Hyper-V Host"}
$CSVheader += "`tHyper-V Host"
$excelcounter++
$allDNSvalues = $excelcounter
$allDNSvaluesCOL = $excelcolumns[$excelcounter]
if(!$CSVoutput){$excelsheet.Cells.Item(1,$allDNSvalues) = "all DNS IP values"}
$CSVheader += "`tall DNS IP values"
$excelcounter++
$hostnamenslookup = $excelcounter
$hostnamenslookupCOL = $excelcolumns[$excelcounter]
if(!$CSVoutput){$excelsheet.Cells.Item(1,$hostnamenslookup) = "hostname nslookup"}
$CSVheader += "`thostname nslookup"
$excelcounter++
$checknslookupagainstpingIP = $excelcounter
$checknslookupagainstpingIPCOL = $excelcolumns[$excelcounter]
if(!$CSVoutput){$excelsheet.Cells.Item(1,$checknslookupagainstpingIP) = "check nslookup against ping IP"}
$CSVheader += "`tcheck nslookup against ping IP"
$excelcounter++
$ADName = $excelcounter
$ADNameCOL = $excelcolumns[$excelcounter]
if(!$CSVoutput){$excelsheet.Cells.Item(1,$ADName) = "AD-Name"}
$CSVheader += "`tAD-Name"
$excelcounter++
$Domain = $excelcounter
$DomainCOL = $excelcolumns[$excelcounter]
if(!$CSVoutput){$excelsheet.Cells.Item(1,$Domain) = "Domain"}
$CSVheader += "`tDomain"
$excelcounter++
$ADEnabled = $excelcounter
$ADEnabledCOL = $excelcolumns[$excelcounter]
if(!$CSVoutput){$excelsheet.Cells.Item(1,$ADEnabled) = "AD-Enabled"}
$CSVheader += "`tAD-Enabled"
$excelcounter++
$ADIPv4Address = $excelcounter
$ADIPv4AddressCOL = $excelcolumns[$excelcounter]
if(!$CSVoutput){$excelsheet.Cells.Item(1,$ADIPv4Address) = "AD-IPv4Address"}
$CSVheader += "`tAD-IPv4Address"
$excelcounter++
$ADDistinguishedname = $excelcounter
$ADDistinguishednameCOL = $excelcolumns[$excelcounter]
if(!$CSVoutput){$excelsheet.Cells.Item(1,$ADDistinguishedname) = "AD-Distinguishedname"}
$CSVheader += "`tAD-Distinguishedname"
$excelcounter++
<# OLD UNUSED AND NOT UPDATED:
$ServerPurpose = $excelcounter
$ServerPurposeCOL = $excelcolumns[$excelcounter]
$excelsheet.Cells.Item(1,$ServerPurpose) = "Server Purpose"
$excelcounter++
$TechnicalOwner = $excelcounter
$TechnicalOwnerCOL = $excelcolumns[$excelcounter]
$excelsheet.Cells.Item(1,$TechnicalOwner) = "Technical Owner"
$excelcounter++
$ApplicationOwner = $excelcounter
$ApplicationOwnerCOL = $excelcolumns[$excelcounter]
$excelsheet.Cells.Item(1,$ApplicationOwner) = "Application Owner"
$excelcounter++
$BusinessOwner = $excelcounter
$BusinessOwnerCOL = $excelcolumns[$excelcounter]
$excelsheet.Cells.Item(1,$BusinessOwner) = "Business Owner"
$excelcounter++
$ProdNonprodserver = $excelcounter
$ProdNonprodserverCOL = $excelcolumns[$excelcounter]
$excelsheet.Cells.Item(1,$ProdNonprodserver) = "Prod/Non-prod server?"
$excelcounter++
$ServerWarrantyCheck = $excelcounter
$ServerWarrantyCheckCOL = $excelcolumns[$excelcounter]
$excelsheet.Cells.Item(1,$ServerWarrantyCheck) = "Manufacturer Warranty Expiry"
$excelcounter++
$ExtendedWarrantyCheck = $excelcounter
$ExtendedWarrantyCOL = $excelcolumns[$excelcounter]
$excelsheet.Cells.Item(1,$ExtendedWarrantyCheck) = "Extended Warranty Start"
$excelcounter++
$IPXcommvaultstatus = $excelcounter
$IPXcommvaultstatusCOL = $excelcolumns[$excelcounter]
$excelsheet.Cells.Item(1,$IPXcommvaultstatus) = "IPX commvault status"
$excelcounter++
$backuprequired = $excelcounter
$backuprequiredCOL = $excelcolumns[$excelcounter]
$excelsheet.Cells.Item(1,$backuprequired) = "backup required?"
$excelcounter++
$Patchingwavewindow = $excelcounter
$PatchingwavewindowCOL = $excelcolumns[$excelcounter]
$excelsheet.Cells.Item(1,$Patchingwavewindow) = "Patching wave window"
$excelcounter++
#>
$ADOperatingSystem = $excelcounter
$ADOperatingSystemCOL = $excelcolumns[$excelcounter]
if(!$CSVoutput){$excelsheet.Cells.Item(1,$ADOperatingSystem) = "AD-OperatingSystem"}
$CSVheader += "`tAD-OperatingSystem"
$excelcounter++
$ADOperatingSystemServicePack = $excelcounter
$ADOperatingSystemServicePackCOL = $excelcolumns[$excelcounter]
if(!$CSVoutput){$excelsheet.Cells.Item(1,$ADOperatingSystemServicePack) = "AD-OperatingSystemServicePack"}
$CSVheader += "`tAD-OperatingSystemServicePack"
$excelcounter++
$ADSystemVersion = $excelcounter
$ADSystemVersionCOL = $excelcolumns[$excelcounter]
if(!$CSVoutput){$excelsheet.Cells.Item(1,$ADSystemVersion) = "AD-System Version"}
$CSVheader += "`tAD-System Version"
$excelcounter++
$ADCreated = $excelcounter
$ADCreatedCOL = $excelcolumns[$excelcounter]
if(!$CSVoutput){$excelsheet.Cells.Item(1,$ADCreated) = "AD-Created"}
$CSVheader += "`tAD-Created"
$excelcounter++
$ADPasswordLastSet = $excelcounter
$ADPasswordLastSetCOL = $excelcolumns[$excelcounter]
if(!$CSVoutput){$excelsheet.Cells.Item(1,$ADPasswordLastSet) = "AD-PasswordLastSet"}
$CSVheader += "`tAD-PasswordLastSet"
$excelcounter++
$ADModified = $excelcounter
$ADModifiedCOL = $excelcolumns[$excelcounter]
if(!$CSVoutput){$excelsheet.Cells.Item(1,$ADModified) = "AD-Modified"}
$CSVheader += "`tAD-Modified"
$excelcounter++
$ADDescription = $excelcounter
$ADDescriptionCOL = $excelcolumns[$excelcounter]
if(!$CSVoutput){$excelsheet.Cells.Item(1,$ADDescription) = "AD-Description"}
$CSVheader += "`tAD-Description"
$excelcounter++
$WMIDescription = $excelcounter
$WMIDescriptionCOL = $excelcolumns[$excelcounter]
if(!$CSVoutput){$excelsheet.Cells.Item(1,$WMIDescription) = "WMI-Description"}
$CSVheader += "`tWMI-Description"
$excelcounter++
$WMIComputerName = $excelcounter
$WMIComputerNameCOL = $excelcolumns[$excelcounter]
if(!$CSVoutput){$excelsheet.Cells.Item(1,$WMIComputerName) = "WMI-Computer Name"}
$CSVheader += "`tWMI-Computer Name"
$excelcounter++
$WMIOperatingSystem = $excelcounter
$WMIOperatingSystemCOL = $excelcolumns[$excelcounter]
if(!$CSVoutput){$excelsheet.Cells.Item(1,$WMIOperatingSystem) = "WMI-Operating System"}
$CSVheader += "`tWMI-Operating System"
$excelcounter++
$WMISPVersion = $excelcounter
$WMISPVersionCOL = $excelcolumns[$excelcounter]
if(!$CSVoutput){$excelsheet.Cells.Item(1,$WMISPVersion) = "WMI-SP Version"}
$CSVheader += "`tWMI-SP Version"
$excelcounter++
$WMIOSVersion = $excelcounter
$WMIOSVersionCOL = $excelcolumns[$excelcounter]
if(!$CSVoutput){$excelsheet.Cells.Item(1,$WMIOSVersion) = "WMI-OS Version"}
$CSVheader += "`tWMI-OS Version"
$excelcounter++
$WMISystemDirectory = $excelcounter
$WMISystemDirectoryCOL = $excelcolumns[$excelcounter]
if(!$CSVoutput){$excelsheet.Cells.Item(1,$WMISystemDirectory) = "WMI-SystemDirectory"}
$CSVheader += "`tWMI-SystemDirectory"
$excelcounter++
$WMIOrganisation = $excelcounter
$WMIOrganisationCOL = $excelcolumns[$excelcounter]
if(!$CSVoutput){$excelsheet.Cells.Item(1,$WMIOrganisation) = "WMI-Organisation"}
$CSVheader += "`tWMI-Organisation"
$excelcounter++
$WMIManufacturer = $excelcounter
$WMIManufacturerCOL = $excelcolumns[$excelcounter]
if(!$CSVoutput){$excelsheet.Cells.Item(1,$WMIManufacturer) = "WMI-Manufacturer"}
$CSVheader += "`tWMI-Manufacturer"
$excelcounter++
$ComputerSystemModel = $excelcounter
$ComputerSystemModelCOL = $excelcolumns[$excelcounter]
if(!$CSVoutput){$excelsheet.Cells.Item(1,$ComputerSystemModel) = "WMI-System Model"}
$CSVheader += "`tWMI-System Model"
$excelcounter++
$WMISerial = $excelcounter
$WMISerialCOL = $excelcolumns[$excelcounter]
if(!$CSVoutput){$excelsheet.Cells.Item(1,$WMISerial) = "WMI-Serial"}
$CSVheader += "`tWMI-Serial"
$excelcounter++
$WMIProcessorName = $excelcounter
$WMIProcessorNameCOL = $excelcolumns[$excelcounter]
if(!$CSVoutput){$excelsheet.Cells.Item(1,$WMIProcessorName) = "WMI-ProcessorName"}
$CSVheader += "`tWMI-ProcessorName"
$excelcounter++
$WMICPUSockets = $excelcounter
$WMICPUSocketsCOL = $excelcolumns[$excelcounter]
if(!$CSVoutput){$excelsheet.Cells.Item(1,$WMICPUSockets) = "WMI-CPU Sockets"}
$CSVheader += "`tWMI-CPU Sockets"
$excelcounter++
$WMICoresperCPU = $excelcounter
$WMICoresperCPUCOL = $excelcolumns[$excelcounter]
if(!$CSVoutput){$excelsheet.Cells.Item(1,$WMICoresperCPU) = "WMI-Cores per CPU"}
$CSVheader += "`tWMI-Cores per CPU"
$excelcounter++
$WMITotalCores = $excelcounter
$WMITotalCoresCOL = $excelcolumns[$excelcounter]
if(!$CSVoutput){$excelsheet.Cells.Item(1,$WMITotalCores) = "WMI-Total Cores"}
$CSVheader += "`tWMI-Total Cores"
$excelcounter++
$WMIProcessorsperCPU = $excelcounter
$WMIProcessorsperCPUCOL = $excelcolumns[$excelcounter]
if(!$CSVoutput){$excelsheet.Cells.Item(1,$WMIProcessorsperCPU) = "WMI-Processors per CPU"}
$CSVheader += "`tWMI-Processors per CPU"
$excelcounter++
$WMITotalProcessors = $excelcounter
$WMITotalProcessorsCOL = $excelcolumns[$excelcounter]
if(!$CSVoutput){$excelsheet.Cells.Item(1,$WMITotalProcessors) = "WMI-Total Processors"}
$CSVheader += "`tWMI-Total Processors"
$excelcounter++
$WMIMemoryDIMMs = $excelcounter
$WMIMemoryDIMMsCOL = $excelcolumns[$excelcounter]
if(!$CSVoutput){$excelsheet.Cells.Item(1,$WMIMemoryDIMMs) = "WMI-MemoryDIMMs"}
$CSVheader += "`tWMI-MemoryDIMMs"
$excelcounter++
$WMIMemoryGB = $excelcounter
$WMIMemoryGBCOL = $excelcolumns[$excelcounter]
if(!$CSVoutput){$excelsheet.Cells.Item(1,$WMIMemoryGB) = "WMI-Memory (GB)"}
$CSVheader += "`tWMI-Memory (GB)"
$excelcounter++
$WMIMemoryCount = $excelcounter
$WMIMemoryCountCOL = $excelcolumns[$excelcounter]
if(!$CSVoutput){$excelsheet.Cells.Item(1,$WMIMemoryCount) = "WMI-MemoryCount"}
$CSVheader += "`tWMI-MemoryCount"
$excelcounter++
$WMIIPAddress1 = $excelcounter
$WMIIPAddress1COL = $excelcolumns[$excelcounter]
if(!$CSVoutput){$excelsheet.Cells.Item(1,$WMIIPAddress1) = "WMI-NIC1 IP Address"}
$CSVheader += "`tWMI-NIC1 IP Address"
$excelcounter++
$WMImask1 = $excelcounter
$WMImask1COL = $excelcolumns[$excelcounter]
if(!$CSVoutput){$excelsheet.Cells.Item(1,$WMImask1) = "WMI-NIC1 Mask"}
$CSVheader += "`tWMI-NIC1 Mask"
$excelcounter++
$WMIDHCPenabled1 = $excelcounter
$WMIDHCPenabled1COL = $excelcolumns[$excelcounter]
if(!$CSVoutput){$excelsheet.Cells.Item(1,$WMIDHCPenabled1) = "WMI-NIC1 DHCP?"}
$CSVheader += "`tWMI-NIC1 DHCP?"
$excelcounter++
$WMIGateway1 = $excelcounter
$WMIGateway1COL = $excelcolumns[$excelcounter]
if(!$CSVoutput){$excelsheet.Cells.Item(1,$WMIGateway1) = "WMI-NIC1 Gateway"}
$CSVheader += "`tWMI-NIC1 Gateway"
$excelcounter++
$WMIPrimaryDNS1 = $excelcounter
$WMIPrimaryDNS1COL = $excelcolumns[$excelcounter]
if(!$CSVoutput){$excelsheet.Cells.Item(1,$WMIPrimaryDNS1) = "WMI-NIC1 Primary DNS"}
$CSVheader += "`tWMI-NIC1 Primary DNS"
$excelcounter++
$WMISecondaryDNS1 = $excelcounter
$WMISecondaryDNS1COL = $excelcolumns[$excelcounter]
if(!$CSVoutput){$excelsheet.Cells.Item(1,$WMISecondaryDNS1) = "WMI-NIC1 Secondary DNS"}
$CSVheader += "`tWMI-NIC1 Secondary DNS"
$excelcounter++
$WMIOtherDNS1 = $excelcounter
$WMIOtherDNS1COL = $excelcolumns[$excelcounter]
if(!$CSVoutput){$excelsheet.Cells.Item(1,$WMIOtherDNS1) = "WMI-NIC1 Other DNS"}
$CSVheader += "`tWMI-NIC1 Other DNS"
$excelcounter++
$WMIPrimaryWINS1 = $excelcounter
$WMIPrimaryWINS1COL = $excelcolumns[$excelcounter]
if(!$CSVoutput){$excelsheet.Cells.Item(1,$WMIPrimaryWINS1) = "WMI-NIC1 Primary WINS"}
$CSVheader += "`tWMI-NIC1 Primary WINS"
$excelcounter++
$WMISecondaryWINS1 = $excelcounter
$WMISecondaryWINS1COL = $excelcolumns[$excelcounter]
if(!$CSVoutput){$excelsheet.Cells.Item(1,$WMISecondaryWINS1) = "WMI-NIC1 Secondary WINS"}
$CSVheader += "`tWMI-NIC1 Secondary WINS"
$excelcounter++
$WMIMAC1 = $excelcounter
$WMIMAC1COL = $excelcolumns[$excelcounter]
if(!$CSVoutput){$excelsheet.Cells.Item(1,$WMIMAC1) = "WMI-NIC1 MAC"}
$CSVheader += "`tWMI-NIC1 MAC"
$excelcounter++
$WMIIPAddress2 = $excelcounter
$WMIIPAddress2COL = $excelcolumns[$excelcounter]
if(!$CSVoutput){$excelsheet.Cells.Item(1,$WMIIPAddress2) = "WMI-NIC2 IP Address"}
$CSVheader += "`tWMI-NIC2 IP Address"
$excelcounter++
$WMImask2 = $excelcounter
$WMImask2COL = $excelcolumns[$excelcounter]
if(!$CSVoutput){$excelsheet.Cells.Item(1,$WMImask2) = "WMI-NIC2 Mask"}
$CSVheader += "`tWMI-NIC2 Mask"
$excelcounter++
$WMIDHCPenabled2 = $excelcounter
$WMIDHCPenabled2COL = $excelcolumns[$excelcounter]
if(!$CSVoutput){$excelsheet.Cells.Item(1,$WMIDHCPenabled2) = "WMI-NIC2 DHCP?"}
$CSVheader += "`tWMI-NIC2 DHCP?"
$excelcounter++
$WMIGateway2 = $excelcounter
$WMIGateway2COL = $excelcolumns[$excelcounter]
if(!$CSVoutput){$excelsheet.Cells.Item(1,$WMIGateway2) = "WMI-NIC2 Gateway"}
$CSVheader += "`tWMI-NIC2 Gateway"
$excelcounter++
$WMIPrimaryDNS2 = $excelcounter
$WMIPrimaryDNS2COL = $excelcolumns[$excelcounter]
if(!$CSVoutput){$excelsheet.Cells.Item(1,$WMIPrimaryDNS2) = "WMI-NIC2 Primary DNS"}
$CSVheader += "`tWMI-NIC2 Primary DNS"
$excelcounter++
$WMISecondaryDNS2 = $excelcounter
$WMISecondaryDNS2COL = $excelcolumns[$excelcounter]
if(!$CSVoutput){$excelsheet.Cells.Item(1,$WMISecondaryDNS2) = "WMI-NIC2 Secondary DNS"}
$CSVheader += "`tWMI-NIC2 Secondary DNS"
$excelcounter++
$WMIOtherDNS2 = $excelcounter
$WMIOtherDNS2COL = $excelcolumns[$excelcounter]
if(!$CSVoutput){$excelsheet.Cells.Item(1,$WMIOtherDNS2) = "WMI-NIC2 Other DNS"}
$CSVheader += "`tWMI-NIC2 Other DNS"
$excelcounter++
$WMIPrimaryWINS2 = $excelcounter
$WMIPrimaryWINS2COL = $excelcolumns[$excelcounter]
if(!$CSVoutput){$excelsheet.Cells.Item(1,$WMIPrimaryWINS2) = "WMI-NIC2 Primary WINS"}
$CSVheader += "`tWMI-NIC2 Primary WINS"
$excelcounter++
$WMISecondaryWINS2 = $excelcounter
$WMISecondaryWINS2COL = $excelcolumns[$excelcounter]
if(!$CSVoutput){$excelsheet.Cells.Item(1,$WMISecondaryWINS2) = "WMI-NIC2 Secondary WINS"}
$CSVheader += "`tWMI-NIC2 Secondary WINS"
$excelcounter++
$WMIMAC2 = $excelcounter
$WMIMAC2COL = $excelcolumns[$excelcounter]
if(!$CSVoutput){$excelsheet.Cells.Item(1,$WMIMAC2) = "WMI-NIC2 MAC"}
$CSVheader += "`tWMI-NIC2 MAC"
$excelcounter++
$WMIIPAddress3 = $excelcounter
$WMIIPAddress3COL = $excelcolumns[$excelcounter]
if(!$CSVoutput){$excelsheet.Cells.Item(1,$WMIIPAddress3) = "WMI-NIC3 IP Address"}
$CSVheader += "`tWMI-NIC3 IP Address"
$excelcounter++
$WMImask3 = $excelcounter
$WMImask3COL = $excelcolumns[$excelcounter]
if(!$CSVoutput){$excelsheet.Cells.Item(1,$WMImask3) = "WMI-NIC3 Mask"}
$CSVheader += "`tWMI-NIC3 Mask"
$excelcounter++
$WMIDHCPenabled3 = $excelcounter
$WMIDHCPenabled3COL = $excelcolumns[$excelcounter]
if(!$CSVoutput){$excelsheet.Cells.Item(1,$WMIDHCPenabled3) = "WMI-NIC3 DHCP?"}
$CSVheader += "`tWMI-NIC3 DHCP?"
$excelcounter++
$WMIGateway3 = $excelcounter
$WMIGateway3COL = $excelcolumns[$excelcounter]
if(!$CSVoutput){$excelsheet.Cells.Item(1,$WMIGateway3) = "WMI-NIC3 Gateway"}
$CSVheader += "`tWMI-NIC3 Gateway"
$excelcounter++
$WMIPrimaryDNS3 = $excelcounter
$WMIPrimaryDNS3COL = $excelcolumns[$excelcounter]
if(!$CSVoutput){$excelsheet.Cells.Item(1,$WMIPrimaryDNS3) = "WMI-NIC3 Primary DNS"}
$CSVheader += "`tWMI-NIC3 Primary DNS"
$excelcounter++
$WMISecondaryDNS3 = $excelcounter
$WMISecondaryDNS3COL = $excelcolumns[$excelcounter]
if(!$CSVoutput){$excelsheet.Cells.Item(1,$WMISecondaryDNS3) = "WMI-NIC3 Secondary DNS"}
$CSVheader += "`tWMI-NIC3 Secondary DNS"
$excelcounter++
$WMIOtherDNS3 = $excelcounter
$WMIOtherDNS3COL = $excelcolumns[$excelcounter]
if(!$CSVoutput){$excelsheet.Cells.Item(1,$WMIOtherDNS3) = "WMI-NIC3 Other DNS"}
$CSVheader += "`tWMI-NIC3 Other DNS"
$excelcounter++
$WMIPrimaryWINS3 = $excelcounter
$WMIPrimaryWINS3COL = $excelcolumns[$excelcounter]
if(!$CSVoutput){$excelsheet.Cells.Item(1,$WMIPrimaryWINS3) = "WMI-NIC3 Primary WINS"}
$CSVheader += "`tWMI-NIC3 Primary WINS"
$excelcounter++
$WMISecondaryWINS3 = $excelcounter
$WMISecondaryWINS3COL = $excelcolumns[$excelcounter]
if(!$CSVoutput){$excelsheet.Cells.Item(1,$WMISecondaryWINS3) = "WMI-NIC3 Secondary WINS"}
$CSVheader += "`tWMI-NIC3 Secondary WINS"
$excelcounter++
$WMIMAC3 = $excelcounter
$WMIMAC3COL = $excelcolumns[$excelcounter]
if(!$CSVoutput){$excelsheet.Cells.Item(1,$WMIMAC3) = "WMI-NIC3 MAC"}
$CSVheader += "`tWMI-NIC3 MAC"
$excelcounter++
$WMIIPAddress4 = $excelcounter
$WMIIPAddress4COL = $excelcolumns[$excelcounter]
if(!$CSVoutput){$excelsheet.Cells.Item(1,$WMIIPAddress4) = "WMI-NIC4 IP Address"}
$CSVheader += "`tWMI-NIC4 IP Address"
$excelcounter++
$WMImask4 = $excelcounter
$WMImask4COL = $excelcolumns[$excelcounter]
if(!$CSVoutput){$excelsheet.Cells.Item(1,$WMImask4) = "WMI-NIC4 Mask"}
$CSVheader += "`tWMI-NIC4 Mask"
$excelcounter++
$WMIDHCPenabled4 = $excelcounter
$WMIDHCPenabled4COL = $excelcolumns[$excelcounter]
if(!$CSVoutput){$excelsheet.Cells.Item(1,$WMIDHCPenabled4) = "WMI-NIC4 DHCP?"}
$CSVheader += "`tWMI-NIC4 DHCP?"
$excelcounter++
$WMIGateway4 = $excelcounter
$WMIGateway4COL = $excelcolumns[$excelcounter]
if(!$CSVoutput){$excelsheet.Cells.Item(1,$WMIGateway4) = "WMI-NIC4 Gateway"}
$CSVheader += "`tWMI-NIC4 Gateway"
$excelcounter++
$WMIPrimaryDNS4 = $excelcounter
$WMIPrimaryDNS4COL = $excelcolumns[$excelcounter]
if(!$CSVoutput){$excelsheet.Cells.Item(1,$WMIPrimaryDNS4) = "WMI-NIC4 Primary DNS"}
$CSVheader += "`tWMI-NIC4 Primary DNS"
$excelcounter++
$WMISecondaryDNS4 = $excelcounter
$WMISecondaryDNS4COL = $excelcolumns[$excelcounter]
if(!$CSVoutput){$excelsheet.Cells.Item(1,$WMISecondaryDNS4) = "WMI-NIC4 Secondary DNS"}
$CSVheader += "`tWMI-NIC4 Secondary DNS"
$excelcounter++
$WMIOtherDNS4 = $excelcounter
$WMIOtherDNS4COL = $excelcolumns[$excelcounter]
if(!$CSVoutput){$excelsheet.Cells.Item(1,$WMIOtherDNS4) = "WMI-NIC4 Other DNS"}
$CSVheader += "`tWMI-NIC4 Other DNS"
$excelcounter++
$WMIPrimaryWINS4 = $excelcounter
$WMIPrimaryWINS4COL = $excelcolumns[$excelcounter]
if(!$CSVoutput){$excelsheet.Cells.Item(1,$WMIPrimaryWINS4) = "WMI-NIC4 Primary WINS"}
$CSVheader += "`tWMI-NIC4 Primary WINS"
$excelcounter++
$WMISecondaryWINS4 = $excelcounter
$WMISecondaryWINS4COL = $excelcolumns[$excelcounter]
if(!$CSVoutput){$excelsheet.Cells.Item(1,$WMISecondaryWINS4) = "WMI-NIC4 Secondary WINS"}
$CSVheader += "`tWMI-NIC4 Secondary WINS"
$excelcounter++
$WMIMAC4 = $excelcounter
$WMIMAC4COL = $excelcolumns[$excelcounter]
if(!$CSVoutput){$excelsheet.Cells.Item(1,$WMIMAC4) = "WMI-NIC4 MAC"}
$CSVheader += "`tWMI-NIC4 MAC"
$excelcounter++
$WMIIPAddress5 = $excelcounter
$WMIIPAddress5COL = $excelcolumns[$excelcounter]
if(!$CSVoutput){$excelsheet.Cells.Item(1,$WMIIPAddress5) = "WMI-NIC5 IP Address"}
$CSVheader += "`tWMI-NIC5 IP Address"
$excelcounter++
$WMImask5 = $excelcounter
$WMImask5COL = $excelcolumns[$excelcounter]
if(!$CSVoutput){$excelsheet.Cells.Item(1,$WMImask5) = "WMI-NIC5 Mask"}
$CSVheader += "`tWMI-NIC5 Mask"
$excelcounter++
$WMIDHCPenabled5 = $excelcounter
$WMIDHCPenabled5COL = $excelcolumns[$excelcounter]
if(!$CSVoutput){$excelsheet.Cells.Item(1,$WMIDHCPenabled5) = "WMI-NIC5 DHCP?"}
$CSVheader += "`tWMI-NIC5 DHCP?"
$excelcounter++
$WMIGateway5 = $excelcounter
$WMIGateway5COL = $excelcolumns[$excelcounter]
if(!$CSVoutput){$excelsheet.Cells.Item(1,$WMIGateway5) = "WMI-NIC5 Gateway"}
$CSVheader += "`tWMI-NIC5 Gateway"
$excelcounter++
$WMIPrimaryDNS5 = $excelcounter
$WMIPrimaryDNS5COL = $excelcolumns[$excelcounter]
if(!$CSVoutput){$excelsheet.Cells.Item(1,$WMIPrimaryDNS5) = "WMI-NIC5 Primary DNS"}
$CSVheader += "`tWMI-NIC5 Primary DNS"
$excelcounter++
$WMISecondaryDNS5 = $excelcounter
$WMISecondaryDNS5COL = $excelcolumns[$excelcounter]
if(!$CSVoutput){$excelsheet.Cells.Item(1,$WMISecondaryDNS5) = "WMI-NIC5 Secondary DNS"}
$CSVheader += "`tWMI-NIC5 Secondary DNS"
$excelcounter++
$WMIOtherDNS5 = $excelcounter
$WMIOtherDNS5COL = $excelcolumns[$excelcounter]
if(!$CSVoutput){$excelsheet.Cells.Item(1,$WMIOtherDNS5) = "WMI-NIC5 Other DNS"}
$CSVheader += "`tWMI-NIC5 Other DNS"
$excelcounter++
$WMIPrimaryWINS5 = $excelcounter
$WMIPrimaryWINS5COL = $excelcolumns[$excelcounter]
if(!$CSVoutput){$excelsheet.Cells.Item(1,$WMIPrimaryWINS5) = "WMI-NIC5 Primary WINS"}
$CSVheader += "`tWMI-NIC5 Primary WINS"
$excelcounter++
$WMISecondaryWINS5 = $excelcounter
$WMISecondaryWINS5COL = $excelcolumns[$excelcounter]
if(!$CSVoutput){$excelsheet.Cells.Item(1,$WMISecondaryWINS5) = "WMI-NIC5 Secondary WINS"}
$CSVheader += "`tWMI-NIC5 Secondary WINS"
$excelcounter++
$WMIMAC5 = $excelcounter
$WMIMAC5COL = $excelcolumns[$excelcounter]
if(!$CSVoutput){$excelsheet.Cells.Item(1,$WMIMAC5) = "WMI-NIC5 MAC"}
$CSVheader += "`tWMI-NIC5 MAC"
$excelcounter++
$WMIDeviceID = $excelcounter
$WMIDeviceIDCOL = $excelcolumns[$excelcounter]
if(!$CSVoutput){$excelsheet.Cells.Item(1,$WMIDeviceID) = "WMI-HDD1 ID"}
$CSVheader += "`tWMI-HDD1 ID"
$excelcounter++
$WMITotalSize = $excelcounter
$WMITotalSizeCOL = $excelcolumns[$excelcounter]
if(!$CSVoutput){$excelsheet.Cells.Item(1,$WMITotalSize) = "WMI-Total Size1 (GB)"}
$CSVheader += "`tWMI-Total Size1 (GB)"
$excelcounter++
$WMIFreeSpace1 = $excelcounter
$WMIFreeSpace1COL = $excelcolumns[$excelcounter]
if(!$CSVoutput){$excelsheet.Cells.Item(1,$WMIFreeSpace1) = "WMI-Free Space1 (GB)"}
$CSVheader += "`tWMI-Free Space1 (GB)"
$excelcounter++
$WMIFreeSpace2 = $excelcounter
$WMIFreeSpace2COL = $excelcolumns[$excelcounter]
if(!$CSVoutput){$excelsheet.Cells.Item(1,$WMIFreeSpace2) = "WMI-Free Space1 (%)"}
$CSVheader += "`tWMI-Free Space1 (%)"
$excelcounter++
$WMIDriveName = $excelcounter
$WMIDriveNameCOL = $excelcolumns[$excelcounter]
if(!$CSVoutput){$excelsheet.Cells.Item(1,$WMIDriveName) = "WMI-DriveName1"}
$CSVheader += "`tWMI-DriveName1"
$excelcounter++
if(!$CSVoutput){
#SET ALL CONDITIONAL FORMATTING FOR ENTIRE WORKSHEET - modify the range below if server count exceeds 2000
$Selection1 = $excelsheet.Range($connectivityresultsCOL+"2:"+$connectivityresultsCOL+"2000")
$Selection2 = $excelsheet.Range($checkpingIPagainstADCOL+"2:"+$checkpingIPagainstADCOL+"2000")
$Selection3 = $excelsheet.Range($checknslookupagainstpingIPCOL+"2:"+$checknslookupagainstpingIPCOL+"2000")
$Selection4 = $excelsheet.Range($checkWMINameagainstADDNSCOL+"2:"+$checkWMINameagainstADDNSCOL+"2000")
$Selection5 = $excelsheet.Range($MachineNameCOL+"2:"+$MachineNameCOL+"2000")
$Selection6 = $excelsheet.Range($ADEnabledCOL+"2:"+$ADEnabledCOL+"2000")
#set conditional formatting for "ping test IP" column cells based on cell value
$Formula1a = '='+$connectivityresultsCOL+'2="ok"'
$Selection1.FormatConditions.Add(2,0, $Formula1a) | Out-Null
$Selection1.FormatConditions.Item(1).Interior.ColorIndex = 10
$Selection1.FormatConditions.Item(1).Font.ColorIndex = 2
$Formula1b = '=(OR('+$connectivityresultsCOL+'2="PING&DNSFAIL",'+$connectivityresultsCOL+'2="WMIFAIL",'+$connectivityresultsCOL+'2="PINGFAIL",'+$connectivityresultsCOL+'2="DNS&WMIFAIL"))'
$Selection1.FormatConditions.Add(2,0, $Formula1b) | Out-Null
$Selection1.FormatConditions.Item(2).Interior.ColorIndex = 3
$Selection1.FormatConditions.Item(2).Font.ColorIndex = 2
#set conditional formatting for "check ping IP against AD" column cells based on cell value
$Formula2a = '='+$checkpingIPagainstADCOL+'2="same"'
$Selection2.FormatConditions.Add(2,0, $Formula2a) | Out-Null
$Selection2.FormatConditions.Item(1).Interior.ColorIndex = 10
$Selection2.FormatConditions.Item(1).Font.ColorIndex = 2
$Formula2b = '='+$checkpingIPagainstADCOL+'2="fail"'
$Selection2.FormatConditions.Add(2,0, $Formula2b) | Out-Null
$Selection2.FormatConditions.Item(2).Interior.ColorIndex = 3
$Selection2.FormatConditions.Item(2).Font.ColorIndex = 2
#set conditional formatting for "check nslookup against ping IP" column cells based on cell value
$Formula3a = '='+$checknslookupagainstpingIPCOL+'2="same"'
$Selection3.FormatConditions.Add(2,0, $Formula3a) | Out-Null
$Selection3.FormatConditions.Item(1).Interior.ColorIndex = 10
$Selection3.FormatConditions.Item(1).Font.ColorIndex = 2
$Formula3b = '=(OR('+$checknslookupagainstpingIPCOL+'2="fail",'+$checknslookupagainstpingIPCOL+'2="missing"))'
$Selection3.FormatConditions.Add(2,0, $Formula3b) | Out-Null
$Selection3.FormatConditions.Item(2).Interior.ColorIndex = 3
$Selection3.FormatConditions.Item(2).Font.ColorIndex = 2
#set conditional formatting for "check WMI-Name against AD&DNS" column cells based on cell value
$Formula4a = '='+$checkWMINameagainstADDNSCOL+'2="same"'
$Selection4.FormatConditions.Add(2,0, $Formula4a) | Out-Null
$Selection4.FormatConditions.Item(1).Interior.ColorIndex = 10
$Selection4.FormatConditions.Item(1).Font.ColorIndex = 2
$Formula4b = '='+$checkWMINameagainstADDNSCOL+'2="fail"'
$Selection4.FormatConditions.Add(2,0, $Formula4b) | Out-Null
$Selection4.FormatConditions.Item(2).Interior.ColorIndex = 3
$Selection4.FormatConditions.Item(2).Font.ColorIndex = 2
#set conditional formatting for "check nslookup-IP nslookup against Machine Name" column cells based on cell value
$Formula5b = '=(AND('+$MachineNameCOL+'2<>'+$nslookupIPnslookupCOL+'2,'+$nslookupIPnslookupCOL+'2<>""))'
$Selection5.FormatConditions.Add(2,0, $Formula5b) | Out-Null
$Selection5.FormatConditions.Item(1).Interior.ColorIndex = 3
$Selection5.FormatConditions.Item(1).Font.ColorIndex = 2
#set conditional formatting for "check WMI-Name against AD&DNS" column cells based on cell value
$Formula6a = '='+$ADEnabledCOL+'2="TRUE"'
$Selection6.FormatConditions.Add(2,0, $Formula6a) | Out-Null
$Selection6.FormatConditions.Item(1).Interior.ColorIndex = 10
$Selection6.FormatConditions.Item(1).Font.ColorIndex = 2
$Formula6b = '='+$ADEnabledCOL+'2="FALSE"'
$Selection6.FormatConditions.Add(2,0, $Formula6b) | Out-Null
$Selection6.FormatConditions.Item(2).Interior.ColorIndex = 3
$Selection6.FormatConditions.Item(2).Font.ColorIndex = 2
###END EXCEL INITIALIZE, COLUMNS DEFINE, and HEADER + CELL VISUAL FORMATTING
}
#--------------------------------------------------------------------------------------------------------#
#THIS NEXT SECTION PERFORMS ALL THE MAIN FUNCTIONALITY INFORMATION GATHERING via AD and WMI QUERIES
#call each $strComputer from the $colComputers array created above, get AD and WMI details for each and populate excel with the results
$todaydate = get-date
$CSVresults = @()
foreach ($strComputer in $colComputers){
if($CSVoutput){write-host "." -nonewline}
$CSVhash = @{}
#reset NIC and HDD array counters since they should start at zero for each computer being processed
$nicincrement = 0
$hddincrement = 0
if($nodomain){$skipADchecks = $true}
#firstly, get values from master-all-server-all-properties AD obtained list ($allServers), and assign values for current server $strComputer to new var $thiscomp
#if however, a server list text file was used ($serverlistmethod = "2"), then AD has not been polled for information yet and will need to be polled for each server now
if($serverlistmethod -eq "1"){
#if AD server list method used, $allservers is all AD properties for each server, so pull $strcomputer.name value and assign to $thiscomp
$thiscomp = $allServers | where-object {$_.name -eq "$strComputer"}
}elseif($serverlistmethod -eq "2"){
#if TEXT file server list method used, poll AD for each $strcomputer and set $thiscomp variable to be the AD properties for that server
if($scandomain -eq "bottlingcompany.com"){
if(!($skipADchecks)){
$thiscomp = Get-ADComputer $strComputer -properties CanonicalName, Created, IPv4Address, Modified, OperatingSystem, OperatingSystemServicePack, passwordlastset, Description -server $scandomain
}
} else {
if(!($skipADchecks)){
$thiscomp = Get-ADComputer -filter {name -eq $strComputer} -properties CanonicalName, Created, IPv4Address, Modified, OperatingSystem, OperatingSystemServicePack, passwordlastset, Description -SearchBase $global:thisdomainDN -server $global:thisdomainROOT
}
}
if(!($thiscomp)){
# if thiscomp is not defined, means the text file listed server comes up in DNS with a FQDN but then is not found in AD so could be in a workgroup or just been deleted from AD, set a flag to skip this server for the AD part and still attempt WMI
if(!($nodomain)){write-host "ERROR: Text file listed computer"$strComputer" does not exist in the "$global:thisdomainROOT" domain ActiveDirectory" -back black -fore red}
$skipADchecks = $true
}
} else {
write-host "ERROR: something went wrong, invalid server list method defined" -back black -fore red; exit
}
if($skipADchecks){$thiscomp = $strComputer}
#delimit canonical name on first slash found to result in domain name
Clear-Variable CurrentCanonicalName
$CurrentCanonicalName = $thiscomp.CanonicalName.substring(0,$thiscomp.CanonicalName.substring(0).indexof("/"))
#set FQDN to allow this program to work in multi domain environment, as ping and WMI requests fail in 2nd domain, so must now use FQDN
Clear-Variable FQDNcomp
if($skipADchecks){
$FQDNcomp = $thiscomp
} else {
$FQDNcomp = $thiscomp.name+"."+$CurrentCanonicalName
}
#get IP by pinging the server
Clear-Variable ping
# PING-OPTION-1 :: the following is the old method which was flawed, test-connection works better as it does not return an IP if the machine is not pingable, while win32_pingstatus might depending on DNS
#$ping = Get-WMIObject Win32_pingstatus -filter "address='$FQDNcomp'" | select ipv4address
# PING-OPTION-2 :: the following is the new method which has issues with powershell v2 and does not work - requires v3 and above
$ping = test-connection -ComputerName $FQDNcomp -count 2 | select ipv4address
#in the event there is a DNS problem where the target computer cannot be pinged by its FQDN, attempt by its hostname
if(!($ping)){$ping = test-connection -ComputerName $FQDNcomp.split(".")[0] -count 2 | select ipv4address}
#get all possible DNS pointer IP values for this computer
Clear-Variable nsvalue
$nsvalue = [system.net.dns]::GetHostAddresses("$FQDNcomp") | select IPAddressToString
#in the event there is a DNS problem where the target computer cannot be nslookup'ed by its FQDN, attempt by its hostname
if(!($nsvalue)){$nsvalue = [system.net.dns]::GetHostAddresses($FQDNcomp.split(".")[0]) | select IPAddressToString}
if(!$CSVoutput){$excelsheet.Cells.Item($intRow, $MachineName) = $strComputer.ToUpper()}
$CSVhash.add($MachineName,$strComputer.ToUpper())
# PING-OPTION-1 :: use the following with ping option 1 - approx. 12 lines above here
#if(!$CSVoutput){$excelsheet.Cells.Item($intRow, $pingtestIP) = $ping.IPV4Address.IPAddressToString}
# PING-OPTION-2 :: use the following with ping option 2 - approx. 12 lines above here
if($ping.IPV4Address[0].IPAddressToString -eq "" -or $ping.IPV4Address[0].IPAddressToString -eq "127.0.0.1"){
if(!$CSVoutput){$excelsheet.Cells.Item($intRow, $pingtestIP) = "ping fail"}
$CSVhash.add($pingtestIP,"ping fail")
} else {
if(!$CSVoutput){$excelsheet.Cells.Item($intRow, $pingtestIP) = $ping.IPV4Address[0].IPAddressToString}
$CSVhash.add($pingtestIP,$ping.IPV4Address[0].IPAddressToString)
}
#since nslookup may return multiple values for one server, add them all into the cell using the Value2 property (10 values max in this case)
if(!$CSVoutput){$excelsheet.Cells.Item($intRow, $allDNSvalues) = $nsvalue[0].IPAddressToString}
$CSVhash.add($allDNSvalues,$nsvalue[0].IPAddressToString)
for($x=1; $x -le 10; $x++){
if (!($nsvalue[$x].IPAddressToString -like "")){
if(!$CSVoutput){$excelsheet.Cells.Item($intRow, $allDNSvalues).Value2 += "; "+$nsvalue[$x].IPAddressToString}
$CSVhash.set_item($allDNSvalues,$CSVhash.get_item($allDNSvalues)+"; "+$nsvalue[0].IPAddressToString)
}
}
#nslookup servername
Clear-Variable nsIP
$nsIP = [System.Net.DNS]::GetHostByName("$FQDNcomp")
if(!$CSVoutput){$excelsheet.Cells.Item($intRow, $hostnamenslookup) = $nsIP.AddressList[0].IPAddressToString}
$CSVhash.add($hostnamenslookup,$nsIP.AddressList[0].IPAddressToString)
Clear-Variable nsHOST
$nsHOST = [System.Net.DNS]::GetHostByAddress($nsIP.AddressList[0])
Clear-Variable nsHOSTname
$nsHOSTname = $nsHOST.HostName
$nsHOSTname = $nsHOSTname.split(".")[0]
if(!$CSVoutput){$excelsheet.Cells.Item($intRow, $nslookupIPnslookup) = $nsHOSTname.ToUpper()}
$CSVhash.add($nslookupIPnslookup,$nsHOSTname.ToUpper())
if(!$CSVoutput){
$excelsheet.Cells.Item($intRow, $ADName) = $thiscomp.name
$excelsheet.Cells.Item($intRow, $Domain) = $CurrentCanonicalName
$excelsheet.Cells.Item($intRow, $ADEnabled) = $thiscomp.enabled
}
$CSVhash.add($ADName,$thiscomp.name)
$CSVhash.add($Domain,$CurrentCanonicalName)
$CSVhash.add($ADEnabled,$thiscomp.enabled)
if(!$CSVoutput){
$excelsheet.Cells.Item($intRow, $ADIPv4Address) = $thiscomp.IPv4Address
$excelsheet.Cells.Item($intRow, $ADDistinguishedname) = $thiscomp.distinguishedname
}
$CSVhash.add($ADIPv4Address,$thiscomp.IPv4Address)
$CSVhash.add($ADDistinguishedname,$thiscomp.distinguishedname)
#the following only applicable to LC domain; populate tech/app/business owner fields with either the found value or question marks if server/environment not found in the imported CSV's, which should then be reviewed and updated
if($scandomain -eq "lc.local"){
$excelsheet.Cells.Item($intRow, $ServerPurpose) = $serverenvironment.get_item($strComputer)
clear-variable envALLowners
$envALLowners = $environmentowner.get_item($serverenvironment.get_item($strComputer))
clear-variable envTECHowner
$envTECHowner = $envALLowners.substring(0,$envALLowners.indexof(","))
clear-variable envAPPowner
$envAPPowner = $envALLowners.substring($envTECHowner.length+1,$envALLowners.substring($envTECHowner.length+1).indexof(","))
clear-variable envBUSowner
$envBUSowner = $envALLowners.substring($envTECHowner.length+$envAPPowner.length+2)
if(!($envTECHowner -eq "")){
$excelsheet.Cells.Item($intRow, $TechnicalOwner) = $envTECHowner
} else {
$excelsheet.Cells.Item($intRow, $TechnicalOwner).Interior.ColorIndex = 3
$excelsheet.Cells.Item($intRow, $TechnicalOwner).Font.ColorIndex = 2
$excelsheet.Cells.Item($intRow, $TechnicalOwner) = " ??? "
}
if(!($envAPPowner -eq "")){
$excelsheet.Cells.Item($intRow, $ApplicationOwner) = $envAPPowner
} else {
$excelsheet.Cells.Item($intRow, $ApplicationOwner).Interior.ColorIndex = 3
$excelsheet.Cells.Item($intRow, $ApplicationOwner).Font.ColorIndex = 2
$excelsheet.Cells.Item($intRow, $ApplicationOwner) = " ??? "
}
if(!($envBUSowner -eq "")){
$excelsheet.Cells.Item($intRow, $BusinessOwner) = $envBUSowner
} else {
$excelsheet.Cells.Item($intRow, $BusinessOwner).Interior.ColorIndex = 3
$excelsheet.Cells.Item($intRow, $BusinessOwner).Font.ColorIndex = 2
$excelsheet.Cells.Item($intRow, $BusinessOwner) = " ??? "
}
$excelsheet.Cells.Item($intRow, $ProdNonprodserver) = $serverproduction.get_item($strComputer)
$excelsheet.Cells.Item($intRow, $IPXcommvaultstatus) = $servercommvault.get_item($strComputer)
$excelsheet.Cells.Item($intRow, $backuprequired) = $serverbackreqd.get_item($strComputer)
$excelsheet.Cells.Item($intRow, $Patchingwavewindow) = " "
}
if(!$CSVoutput){
$excelsheet.Cells.Item($intRow, $ADOperatingSystem) = $thiscomp.OperatingSystem
$excelsheet.Cells.Item($intRow, $ADOperatingSystemServicePack) = $thiscomp.OperatingSystemServicePack
$excelsheet.Cells.Item($intRow, $ADSystemVersion) = $thiscomp.OperatingSystemVersion
$excelsheet.Cells.Item($intRow, $ADCreated) = $thiscomp.Created
$excelsheet.Cells.Item($intRow, $ADPasswordLastSet) = $thiscomp.passwordlastset
$excelsheet.Cells.Item($intRow, $ADModified) = $thiscomp.Modified
$excelsheet.Cells.Item($intRow, $ADDescription) = $thiscomp.description
}
$CSVhash.add($ADOperatingSystem,$thiscomp.OperatingSystem)
$CSVhash.add($ADOperatingSystemServicePack,$thiscomp.OperatingSystemServicePack)
$CSVhash.add($ADSystemVersion,$thiscomp.OperatingSystemVersion)
$CSVhash.add($ADCreated,$thiscomp.Created)
$CSVhash.add($ADPasswordLastSet,$thiscomp.passwordlastset)
$CSVhash.add($ADModified,$thiscomp.Modified)
$CSVhash.add($ADDescription,$thiscomp.description)
# attempt to obtain WMI information if skipPING is true, or ping is not null and not loopback ip and skipWMI is false
if($skipPING -or ($ping.IPV4Address[0].IPAddressToString -ne "" -and $ping.IPV4Address[0].IPAddressToString -ne "127.0.0.1" -and (!($skipWMI)))){
# get details about the Operating System from the computer using WMI
Clear-Variable os
$os = Get-WMIObject Win32_operatingsystem -ComputerName $FQDNcomp
# in the event there is a DNS problem where the target computer cannot be contacted by its FQDN, attempt by its hostname
if(!($os)){
$os = Get-WMIObject Win32_operatingsystem -ComputerName $FQDNcomp.split(".")[0]
if($os){$FQDNcomp = $FQDNcomp.split(".")[0]}
}
# if the above WMI query succeeded, attempt the others, else, break out of the loop and move onto the next computer
if($os){
# add uptime details
$boottime = Get-WmiObject -ComputerName $FQDNcomp win32_operatingsystem | select csname, @{LABEL='LastBootUpTime' ;EXPRESSION={$_.ConverttoDateTime($_.lastbootuptime)}}
if($boottime){
$thisuptime = $todaydate - $boottime.LastBootUpTime
$thisuptime = ""+$thisuptime.days+"D/"+$thisuptime.hours+"H/"+$thisuptime.minutes+"M"
if(!$CSVoutput){$excelsheet.Cells.Item($intRow, $Uptime) = $thisuptime}
$CSVhash.add($Uptime,$thisuptime)
clear-variable boottime,thisuptime
}
# add reboot status details
$reg = [microsoft.win32.registrykey]::OpenRemoteBaseKey('LocalMachine',$FQDNcomp)
if($reg){
# Define a variable to hold the location of a default registry key that exists on all Windows servers
$ValidateKey = "SOFTWARE\\Microsoft\\Windows NT\\CurrentVersion\\Winlogon"
# target reg key is : HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Winlogon\Notify\kwinhook
$validate = $reg.OpenSubKey($ValidateKey)
if($validate){
# Define the variable to hold the location of the registry key to check for
# check known reg key for existence of any properties
# if (Get-ChildItem "HKLM:\SOFTWARE\Microsoft\Windows\CurrentVersion\Component Based Servicing\RebootPending" -EA Ignore) { write-host "item 1 true - Installed Component(s)"; return $true }
$KeyToCheck1 = "SOFTWARE\\Microsoft\\Windows\\CurrentVersion\\Component Based Servicing\\RebootPending"
# basic check for existence of a reg key
# if (Get-Item "HKLM:\SOFTWARE\Microsoft\Windows\CurrentVersion\WindowsUpdate\Auto Update\RebootRequired" -EA Ignore) { write-host "item 2 true - Windows Update"; return $true }
$KeyToCheck2 = "SOFTWARE\\Microsoft\\Windows\\CurrentVersion\\WindowsUpdate\\Auto Update\\RebootRequired"
# check known reg key for existence of a property
# if (Get-ItemProperty "HKLM:\SYSTEM\CurrentControlSet\Control\Session Manager" -Name PendingFileRenameOperations -EA Ignore) { write-host "item 3 true - Pending System File Renames"; return }
$KeyToCheck3 = "SYSTEM\\CurrentControlSet\\Control\\Session Manager"
# Drill down into the desired keys using the OpenSubKey Method
$regkey1 = $reg.OpenSubKey($KeyToCheck1)
if($regkey1.name){
$subkeys1 = $regkey1.GetSubKeyNames()
if($subkeys1){
$thisserver = "Reboot Pending - Installed Components"
$reboot = $true
}
}
# Drill down into the desired keys using the OpenSubKey Method
$regkey3 = $reg.OpenSubKey($KeyToCheck3)
if($regkey3.name){
$subnames3 = $regkey3.GetValueNames()
if($subnames3.contains("PendingFileRenameOperations")){
$value = $regkey3.getvalue("PendingFileRenameOperations")
$actualvalues = @()
# eliminate all empty values in the array and obtain index(es) of all actual values containing full path of system files needing a rename
for($x = 0; $x -lt $value.count; $x++){if($value[$x].length -ne 0){$actualvalues += $x}}
# write-host "index(es) of non-empty values: $actualvalues" -back black -fore green
# cycle through each actual value and determine if all are Windows Defender or not, break from loop if something else found
$onlyignorable = $true
foreach($index in $actualvalues){
# write-host $index "`t" $value[$index] -back black -fore cyan
if($value[$index].tolower().contains("windows defender") -or $value[$index].tolower().contains(":\users\") -or $value[$index].tolower().contains(":\windows\system32\spool") -or $value[$index].tolower().contains(":\windows\temp") -or $value[$index].tolower().contains(":\program") -or $value[$index].tolower().contains(":\windows\ccm") -or $value[$index].tolower().contains(":\windows\fonts") -or $value[$index].tolower().contains(":\windows\fonts") -or $value[$index].tolower().contains("\config.msi") -or $value[$index].tolower().contains(":\windows\appcompat\programs\amcache")){
$onlyignorable = $true
} else {
$onlyignorable = $false
break
}
}
if($onlyignorable){
$thisserver = "Reboot Pending - Only Ignorable System File Renames"
$reboot = $true
} else {
$thisserver = "Reboot Pending - System File Renames due to Patching"
$reboot = $true
}
}
}
# Drill down into the desired keys using the OpenSubKey Method
$regkey2 = $reg.OpenSubKey($KeyToCheck2)
if($regkey2.name){
$thisserver = "Reboot Pending - Windows Update"
$reboot = $true
}
if(!$reboot){
# none of the reg keys returned positive, no reboot pending
$thisserver = "Reboot Not Required - All OK"
}
} else {
$thisserver = "UNKNOWN - PARENT REG KEY NOT FOUND"
}
clear-variable validate
if(!$CSVoutput){$excelsheet.Cells.Item($intRow, $RebootState) = $thisserver}
$CSVhash.add($RebootState,$thisserver)
} else {
$thisserver = "UNKNOWN - REMOTE REGISTRY ACCESS FAILED"
if(!$CSVoutput){$excelsheet.Cells.Item($intRow, $RebootState) = $thisserver}
$CSVhash.add($RebootState,$thisserver)
}
clear-variable reg,regkey1,regkey2,regkey3,thisserver,subkeys1,subnames3,reboot
# add OS details
if(!$CSVoutput){
$excelsheet.Cells.Item($introw, $WMIDescription) = $os.description
$excelsheet.Cells.Item($introw, $WMIComputerName) = $os.PSComputerName
$excelsheet.Cells.Item($introw, $WMIOperatingSystem) = $os.Caption
$excelsheet.Cells.Item($introw, $WMISPVersion) = $os.CSDVersion
$excelsheet.Cells.Item($introw, $WMIOSVersion) = $os.OSArchitecture
$excelsheet.Cells.Item($introw, $WMISystemDirectory) = $os.SystemDirectory
$excelsheet.Cells.Item($introw, $WMIOrganisation) = $os.Organization
}
$CSVhash.add($WMIDescription,$os.description)
$CSVhash.add($WMIComputerName,$os.PSComputerName)
$CSVhash.add($WMIOperatingSystem,$os.Caption)
$CSVhash.add($WMISPVersion,$os.CSDVersion)
$CSVhash.add($WMIOSVersion,$os.OSArchitecture)
$CSVhash.add($WMISystemDirectory,$os.SystemDirectory)
$CSVhash.add($WMIOrganisation,$os.Organization)
#get details about the BIOS from the computer using WMI
Clear-Variable BIOS,manufacturername,currentserial
$BIOS = Get-WMIObject -class "Win32_BIOS" -namespace "root\CIMV2" -computername $FQDNcomp
if($BIOS.Manufacturer -eq "Bochs" -or $BIOS.Manufacturer -eq "OpenStack Foundation" -or $BIOS.Manufacturer -eq "Phoenix Technologies LTD" -or $BIOS.Manufacturer -eq "American Megatrends Inc." -or $BIOS.Manufacturer -eq "Hewlett-Packard" -or $BIOS.Manufacturer -eq "Dell Inc." -or $BIOS.Manufacturer -eq "Microsoft Corporation"){
switch ($BIOS.Manufacturer){
"Bochs" {$manufacturername = "OpenStack Virtual Machine"; break}
"OpenStack Foundation" {$manufacturername = "OpenStack Virtual Machine"; break}
"Phoenix Technologies LTD" {$manufacturername = "VMWare Virtual Machine"; break}
"American Megatrends Inc." {
$manufacturername = "Hyper-V Virtual Machine"
$guestshostsname = determinehost $FQDNcomp
if(!$CSVoutput){$excelsheet.Cells.Item($introw, $hypervhost) = $guestshostsname}
$CSVhash.add($hypervhost,$guestshostsname)
break
}
"Microsoft Corporation" {
$manufacturername = "Hyper-V Virtual Machine"
$guestshostsname = determinehost $FQDNcomp
if(!$CSVoutput){$excelsheet.Cells.Item($introw, $hypervhost) = $guestshostsname}
$CSVhash.add($hypervhost,$guestshostsname)
break
}
"Hewlett-Packard" {$manufacturername = "HP"; break}
"Dell Inc." {$manufacturername = "DELL"; break}
}
if(!$CSVoutput){$excelsheet.Cells.Item($introw, $WMIManufacturer) = $manufacturername}
$CSVhash.add($WMIManufacturer,$manufacturername)
} else {
if(!$CSVoutput){$excelsheet.Cells.Item($introw, $WMIManufacturer) = $BIOS.Manufacturer}
$CSVhash.add($WMIManufacturer,$BIOS.Manufacturer)
}
$currentserial = $BIOS.SerialNumber
$currentserial = $currentserial.replace(" ","")
if(!$CSVoutput){$excelsheet.Cells.Item($introw, $WMISerial) = $currentserial}
$CSVhash.add($WMISerial,$currentserial)
if($scandomain -eq "lc.local"){
$excelsheet.Cells.Item($intRow, $ServerWarrantyCheck) = $serverwarranty.get_item($currentserial)
$excelsheet.Cells.Item($intRow, $ExtendedWarrantyCheck) = $extendedwarranty.get_item($currentserial)
}
#get system model details from the motherboard
Clear-Variable CompSys
$CompSys = Get-WMIObject Win32_ComputerSystem -computername $FQDNcomp | select model
if(!$CSVoutput){$excelsheet.Cells.Item($introw, $ComputerSystemModel) = $CompSys.Model}
$CSVhash.add($ComputerSystemModel,$CompSys.Model)
#get details about the Processor(s) from the computer using WMI
Clear-Variable colCPU
$colCPU = Get-WMIObject Win32_Processor -computername $FQDNcomp | select SocketDesignation, NumberOfCores, NumberOfLogicalProcessors, Name
#the following utilizes two arrays, the first "@($colCPU)" references the array and calls it, the pipe then pulls out each unique SocketDesignation property, then the outer array, the entire line essentially, collects the results and is essentially called upon but with a .count filter which adds all the (now unique) items in the array and returns a count of objects in that array
Clear-Variable sockets
#if socketdesignation property is "None", its a hyper-v vm, and a unique count won't work, so instead do a count of all "None" CPU types
if($colCPU[0].SocketDesignation -eq "None"){
$sockets = @(@($colCPU) | % {$_.SocketDesignation} | select-object).count
}else{
$sockets = @(@($colCPU) | % {$_.SocketDesignation} | select-object -unique).count
}
$cores = 0
$logproc = 0
Clear-Variable CPUname
if($colCPU[0]){
$cores = $colCPU[0].NumberOfCores
$logproc = $colCPU[0].NumberOfLogicalProcessors
$CPUname = $colCPU[0].name
}else{
$cores = $colCPU.NumberOfCores
$logproc = $colCPU.NumberOfLogicalProcessors
$CPUname = $colCPU.name
}
$corestotal = 0
@($colCPU) | % {$corestotal += $_.NumberOfCores}
$logproctotal = 0
@($colCPU) | % {$logproctotal += $_.NumberOfLogicalProcessors}
#if $corestotal still equals zero, the above failed due to WMI not returning the required attributes meaning those CPUs don't poses those attributes, hence single core CPU with no multithreaded processors
if($corestotal -eq 0){
$logproc = $cores = 1
$logproctotal = $corestotal = $cores * $sockets
}
if(!$CSVoutput){
$excelsheet.Cells.Item($introw, $WMIProcessorName) = $CPUname
$excelsheet.Cells.Item($introw, $WMICPUSockets) = $sockets
$excelsheet.Cells.Item($introw, $WMICoresperCPU) = $cores
$excelsheet.Cells.Item($introw, $WMITotalCores) = $corestotal
$excelsheet.Cells.Item($introw, $WMIProcessorsperCPU) = $logproc
$excelsheet.Cells.Item($introw, $WMITotalProcessors) = $logproctotal
}
$CSVhash.add($WMIProcessorName,$CPUname)
$CSVhash.add($WMICPUSockets,$sockets)
$CSVhash.add($WMICoresperCPU,$cores)
$CSVhash.add($WMITotalCores,$corestotal)
$CSVhash.add($WMIProcessorsperCPU,$logproc)
$CSVhash.add($WMITotalProcessors,$logproctotal)
#get details about the Memory from the computer using WMI
Clear-Variable colRAM
$colRAM = Get-WMIObject Win32_PhysicalMemory -computername $FQDNcomp | select Capacity, DeviceLocator
if(!$CSVoutput){$excelsheet.Cells.Item($introw, $WMIMemoryDIMMs) = ""+$colRAM[0].DeviceLocator+" ("+$colRAM[0].Capacity/1024/1024/1024+"GB)"}
$CSVhash.add($WMIMemoryDIMMs,""+$colRAM[0].DeviceLocator+" ("+$colRAM[0].Capacity/1024/1024/1024+"GB)")
for($x=1; $x -le 64; $x++){
if (!($colRAM[$x].DeviceLocator -like "")){
if(!$CSVoutput){$excelsheet.Cells.Item($intRow, $WMIMemoryDIMMs).Value2 += "; "+$colRAM[$x].DeviceLocator+" ("+$colRAM[0].Capacity/1024/1024/1024+"GB)"}
$CSVhash.set_item($WMIMemoryDIMMs,$CSVhash.get_item($WMIMemoryDIMMs)+"; "+$colRAM[$x].DeviceLocator+" ("+$colRAM[0].Capacity/1024/1024/1024+"GB)")
}
}
Clear-Variable colRAMTotal
for($x=0; $x -le 64; $x++){if ((!($colRAM[$x].DeviceLocator -like "")) -and (!($colRAM[$x].DeviceLocator -like "*ROM*"))){$colRAMTotal = $colRAMTotal+($colRAM[$x].Capacity/1024/1024/1024)}}
if(!$CSVoutput){$excelsheet.Cells.Item($intRow, $WMIMemoryGB) = $colRAMTotal}
$CSVhash.add($WMIMemoryGB,$colRAMTotal)
$RAMcount = 0
for($x=0; $x -le 64; $x++){if (!($colRAM[$x].Capacity -like "")){$RAMcount++}}
if(!$CSVoutput){$excelsheet.Cells.Item($intRow, $WMIMemoryCount) = $RAMcount}
$CSVhash.add($WMIMemoryCount,$RAMcount)
#get details about the NICs from the computer using WMI
Clear-Variable NetworkDetails
$NetworkDetails = Get-WMIObject -class "Win32_NetworkAdapterConfiguration" -namespace "root\CIMV2" -ComputerName $FQDNcomp | where{$_.IPEnabled -eq "True"}
$NetworkDetails = Get-WMIObject -class "Win32_NetworkAdapterConfiguration" -namespace "root\CIMV2" -ComputerName $FQDNcomp | where{$_.IPEnabled -eq "True"} | select DHCPEnabled,IPAddress,DefaultIPGateway,DNSDomain,ServiceName,Description,Index,macaddress,WINSPrimaryServer,WINSSecondaryServer,IPSubnet,DNSServerSearchOrder
$nic1 = $WMIIPAddress1
$nic2 = $WMImask1
$nic3 = $WMIDHCPenabled1
$nic4 = $WMIGateway1
$nic5 = $WMIPrimaryDNS1
$nic6 = $WMISecondaryDNS1
$nic7 = $WMIOtherDNS1
$nic8 = $WMIPrimaryWINS1
$nic9 = $WMISecondaryWINS1
$nic10 = $WMIMAC1
if($NetworkDetails[0]){
while(($nicincrement -lt 5) -and ($NetworkDetails[$nicincrement].IPAddress -ne $null)){
if(!$CSVoutput){
$excelsheet.Cells.Item($introw, $nic1) = $NetworkDetails[$nicincrement].IPAddress
$excelsheet.Cells.Item($introw, $nic2) = $NetworkDetails[$nicincrement].IPSubnet
$excelsheet.Cells.Item($introw, $nic3) = $NetworkDetails[$nicincrement].DHCPEnabled
$excelsheet.Cells.Item($introw, $nic4) = $NetworkDetails[$nicincrement].DefaultIPGateway
$excelsheet.Cells.Item($introw, $nic5) = $NetworkDetails[$nicincrement].DNSServerSearchOrder[0]
$excelsheet.Cells.Item($introw, $nic6) = $NetworkDetails[$nicincrement].DNSServerSearchOrder[1]
$excelsheet.Cells.Item($introw, $nic7) = $NetworkDetails[$nicincrement].DNSServerSearchOrder[2]
$excelsheet.Cells.Item($introw, $nic8) = $NetworkDetails[$nicincrement].WINSPrimaryServer
$excelsheet.Cells.Item($introw, $nic9) = $NetworkDetails[$nicincrement].WINSSecondaryServer
$excelsheet.Cells.Item($introw, $nic10) = $NetworkDetails[$nicincrement].MACAddress
}
$CSVhash.add($nic1,$NetworkDetails[$nicincrement].IPAddress)
$CSVhash.add($nic2,$NetworkDetails[$nicincrement].IPSubnet)
$CSVhash.add($nic3,$NetworkDetails[$nicincrement].DHCPEnabled)
$CSVhash.add($nic4,$NetworkDetails[$nicincrement].DefaultIPGateway)
$CSVhash.add($nic5,$NetworkDetails[$nicincrement].DNSServerSearchOrder[0])
$CSVhash.add($nic6,$NetworkDetails[$nicincrement].DNSServerSearchOrder[1])
$CSVhash.add($nic7,$NetworkDetails[$nicincrement].DNSServerSearchOrder[2])
$CSVhash.add($nic8,$NetworkDetails[$nicincrement].WINSPrimaryServer)
$CSVhash.add($nic9,$NetworkDetails[$nicincrement].WINSSecondaryServer)
$CSVhash.add($nic10,$NetworkDetails[$nicincrement].MACAddress)
#the following appends additional secondary DNS servers to the "WMI-Other DNS" column cell, upto a maximum of 10 in this case
for($x=3; $x -le 13; $x++){
if (!($NetworkDetails[$nicincrement].DNSServerSearchOrder[$x] -like "")){
if(!$CSVoutput){$excelsheet.Cells.Item($intRow, $nic7).Value2 += "; "+$NetworkDetails[$nicincrement].DNSServerSearchOrder[$x]}
$CSVhash.set_item($nic7,$CSVhash.get_item($nic7)+"; "+$NetworkDetails[$nicincrement].DNSServerSearchOrder[$x])
}
}
#increment the row index to the next NIC adapter series of 10 columns, break if exceeding the maximum of 5 NIC's that are hardcoded due to excel limitation of having only one series of dynamic columns - HDD's
$nic1 = $nic1 + 10
$nic2 = $nic2 + 10
$nic3 = $nic3 + 10
$nic4 = $nic4 + 10
$nic5 = $nic5 + 10
$nic6 = $nic6 + 10
$nic7 = $nic7 + 10
$nic8 = $nic8 + 10
$nic9 = $nic9 + 10
$nic10 = $nic10 + 10
#increment NIC array counter and attempt computer name compare against current computer name, if same, while loop repeats, else it moves on and next time in this while loop it will start from the previously incremented position
$nicincrement++
#since 7 columns per NIC, and 5 NICs max in excel spreadsheet, break from while loop if $nic1 has been incremented past the acceptable value, ie. don't add any more NICs to excel, 5 is max
if($nic1 -eq ($WMIIPAddress1+(5*7))){
break
}
}
}else{
if(!$CSVoutput){
$excelsheet.Cells.Item($introw, $nic1) = $NetworkDetails.IPAddress
$excelsheet.Cells.Item($introw, $nic2) = $NetworkDetails.IPSubnet
$excelsheet.Cells.Item($introw, $nic3) = $NetworkDetails.DHCPEnabled
$excelsheet.Cells.Item($introw, $nic4) = $NetworkDetails.DefaultIPGateway
$excelsheet.Cells.Item($introw, $nic5) = $NetworkDetails.DNSServerSearchOrder[0]
$excelsheet.Cells.Item($introw, $nic6) = $NetworkDetails.DNSServerSearchOrder[1]
$excelsheet.Cells.Item($introw, $nic7) = $NetworkDetails.DNSServerSearchOrder[2]
$excelsheet.Cells.Item($introw, $nic8) = $NetworkDetails.WINSPrimaryServer
$excelsheet.Cells.Item($introw, $nic9) = $NetworkDetails.WINSSecondaryServer
$excelsheet.Cells.Item($introw, $nic10) = $NetworkDetails.MACAddress
}
$CSVhash.add($nic1,$NetworkDetails.IPAddress)
$CSVhash.add($nic2,$NetworkDetails.IPSubnet)
$CSVhash.add($nic3,$NetworkDetails.DHCPEnabled)
$CSVhash.add($nic4,$NetworkDetails.DefaultIPGateway)
$CSVhash.add($nic5,$NetworkDetails.DNSServerSearchOrder[0])
$CSVhash.add($nic6,$NetworkDetails.DNSServerSearchOrder[1])
$CSVhash.add($nic7,$NetworkDetails.DNSServerSearchOrder[2])
$CSVhash.add($nic8,$NetworkDetails.WINSPrimaryServer)
$CSVhash.add($nic9,$NetworkDetails.WINSSecondaryServer)
$CSVhash.add($nic10,$NetworkDetails.MACAddress)
#the following appends additional secondary DNS servers to the "WMI-Other DNS" column cell, upto a maximum of 10 in this case
for($x=3; $x -le 13; $x++){
if (!($NetworkDetails.DNSServerSearchOrder[$x] -like "")){
if(!$CSVoutput){$excelsheet.Cells.Item($intRow, $WMIOtherDNS1).Value2 += "; "+$NetworkDetails.DNSServerSearchOrder[$x]}
$CSVhash.set_item($WMIOtherDNS1,$CSVhash.get_item($WMIOtherDNS1)+"; "+$NetworkDetails.DNSServerSearchOrder[$x])
}
}
}
#get details about the Logical Disk(s) from the computer using WMI
Clear-Variable colDisks
$colDisks = Get-WMIObject Win32_LogicalDisk -computername $FQDNcomp -Filter "DriveType = 3"
#create new variables for dynamically creating more columns in the excel workbook when additional logical drives are found
$hdd1 = $WMIDeviceID
$hdd2 = $WMITotalSize
$hdd3 = $WMIFreeSpace1
$hdd4 = $WMIFreeSpace2
$hdd5 = $WMIDriveName
#this loop will add more columns to excel for each new logical disk found as required
Clear-Variable objDisk
$hddid = 2
$forcounter = 1
foreach ($objDisk in $colDisks){
#only create headers if more than one set of hdd data
if($forcounter -gt 1){
#create next set of hdd headers
if(!$CSVoutput){
$excelsheet.Cells.Item(1, $hdd1) = "WMI-HDD"+$hddid+" ID"
$excelsheet.Cells.Item(1, $hdd2) = "WMI-Total Size"+$hddid+" (GB)"
$excelsheet.Cells.Item(1, $hdd3) = "WMI-Free Space"+$hddid+" (GB)"
$excelsheet.Cells.Item(1, $hdd4) = "WMI-Free Space"+$hddid+" (%)"
$excelsheet.Cells.Item(1, $hdd5) = "WMI-DriveName"+$hddid
} else {
$hddheader = "WMI-HDD"+$hddid+" ID"
if(!($CSVheader.contains($hddheader))){
$CSVheader += "`tWMI-HDD$hddid ID"
$CSVheader += "`tWMI-Total Size$hddid (GB)"
$CSVheader += "`tWMI-Free Space$hddid (GB)"
$CSVheader += "`tWMI-Free Space$hddid (%)"
$CSVheader += "`tWMI-DriveName$hddid"
}
$hddid++
}
}
# add data for the current disk below the current headers
if(!$CSVoutput){
$excelsheet.Cells.Item($intRow, $hdd1) = $objDisk.DeviceID
$excelsheet.Cells.Item($intRow, $hdd2) = "{0:N0}" -f ($objDisk.Size/1GB)
$excelsheet.Cells.Item($intRow, $hdd3) = "{0:N0}" -f ($objDisk.FreeSpace/1GB)
$excelsheet.Cells.Item($intRow, $hdd4) = "{0:P0}" -f ([double]$objDisk.FreeSpace/[double]$objDisk.Size)
$excelsheet.Cells.Item($introw, $hdd5) = $objDisk.volumename
}
$CSVhash.add($hdd1,$objDisk.DeviceID)
$CSVhash.add($hdd2,"{0:N0}" -f ($objDisk.Size/1GB))
$CSVhash.add($hdd3,"{0:N0}" -f ($objDisk.FreeSpace/1GB))
$CSVhash.add($hdd4,"{0:P0}" -f ([double]$objDisk.FreeSpace/[double]$objDisk.Size))
$CSVhash.add($hdd5,$objDisk.volumename)
$hdd1 = $hdd1 + 5
$hdd2 = $hdd2 + 5
$hdd3 = $hdd3 + 5
$hdd4 = $hdd4 + 5
$hdd5 = $hdd5 + 5
$forcounter++
}
} else {
# wmi failed for current server
write-host "`nWARNING: Remote OS-WMI query failed for '$FQDNcomp'" -back red -fore yellow
$hostip = $ping.IPV4Address[0].IPAddressToString
if($hostip -ne $null -and $nsvalue -ne $null -and $os.PSComputerName -ne $null){
$connectivityresultsvalue = "ok"
} elseif($hostip -ne $null -and $nsvalue -ne $null -and $os.PSComputerName -eq $null){
$connectivityresultsvalue = "WMIFAIL"
} elseif($hostip -ne $null -and $nsvalue -eq $null -and $os.PSComputerName -ne $null){
$connectivityresultsvalue = "DNSFAIL"
} elseif($hostip -ne $null -and $nsvalue -eq $null -and $os.PSComputerName -eq $null){
$connectivityresultsvalue = "DNS&WMIFAIL"
} elseif($hostip -eq $null -and $nsvalue -eq $null){
$connectivityresultsvalue = "PING&DNSFAIL"
} else {
$connectivityresultsvalue = "PINGFAIL"
}
if(!$CSVoutput){$excelsheet.Cells.Item($intRow, $connectivityresults) = $connectivityresultsvalue}
$CSVhash.add($connectivityresults,$connectivityresultsvalue)
# adds formula to cell to check "ping test IP" and "AD-IPv4Address" : if ping IP is not blank and they match = same ; else = fail
#if(!$CSVoutput){$excelsheet.Cells.Item($intRow, $checkpingIPagainstAD) = '=IF(AND('+$pingtestIPCOL+$intRow+'<>"",('+$pingtestIPCOL+$intRow+'='+$ADIPv4AddressCOL+$intRow+')),"same","fail")'}
clear-variable IPADcheck
if($hostip -ne $null -and $hostip -eq $thiscomp.IPv4Address){
$IPADcheck = "same"
} else {
$IPADcheck = "fail"
}
if(!$CSVoutput){$excelsheet.Cells.Item($intRow, $checkpingIPagainstAD) = $IPADcheck}
$CSVhash.add($checkpingIPagainstAD,$IPADcheck)
# adds formula to cell to check "ping test IP" and "nslookup" : if ping IP is not blank and they match = same ; else = fail
#if(!$CSVoutput){$excelsheet.Cells.Item($intRow, $checknslookupagainstpingIP) = '=IF(AND('+$pingtestIPCOL+$intRow+'<>"",('+$pingtestIPCOL+$intRow+'='+$allDNSvaluesCOL+$intRow+')),"same","fail")'}
clear-variable NSIPcheck
if($hostip -ne $null -and $nsvalue -ne $null){
if($nsvalue.count -eq $null){$thiscount = 1}else{$thiscount = $nsvalue.count}
for($counter = 0; $counter -lt $thiscount; $counter++){
if($hostip -eq $nsvalue[$counter].IPAddressToString){
$NSIPcheck = "same"
break
} elseif($counter+1 -eq $thiscount){
#none of the DNS IP's match the ping result IP
$NSIPcheck = "missing"
}
}
} else {
$NSIPcheck = "fail"
}
if(!$CSVoutput){
$excelsheet.Cells.Item($intRow, $checknslookupagainstpingIP) = $NSIPcheck
#wmi failed/skipped so set WMI compare check to fail
$excelsheet.Cells.Item($intRow, $checkWMINameagainstADDNS) = "fail"
}
$CSVhash.add($checknslookupagainstpingIP,$NSIPcheck)
$CSVhash.add($checkWMINameagainstADDNS,"fail")
# output csv contents for debugging
$CSVline = ""
for($x = 0; $x -lt $(($CSVheader.toCharArray() | Where-Object {$_ -eq "`t"} | Measure-Object).Count+1); $x++){$CSVline += "`t$($CSVhash.get_item($x))"}
$CSVline = $CSVline.trimstart("`t")
$CSVresults += $CSVline
$intRow++
clear-variable skipADchecks, thiscomp, namecheck, pinglocation, hostip, connectivityresultsvalue, IPADcheck, NSIPcheck
continue
}
} else {
if($skipWMI){
# server is pingable and in a semi-crashed state and hanging on WMI queries, this is for an exclude list run of servers via the -serverlist method following identification of servers hanging on WMI attempts
write-host "`nSkipping WMI attempts for '$FQDNcomp'" -back red -fore yellow
$connectivityresultsvalue = "WMIFAIL"
if(!$CSVoutput){$excelsheet.Cells.Item($intRow, $connectivityresults) = $connectivityresultsvalue}
$CSVhash.add($connectivityresults,$connectivityresultsvalue)
# adds formula to cell to check "ping test IP" and "AD-IPv4Address" : if ping IP is not blank and they match = same ; else = fail
#if(!$CSVoutput){$excelsheet.Cells.Item($intRow, $checkpingIPagainstAD) = '=IF(AND('+$pingtestIPCOL+$intRow+'<>"",('+$pingtestIPCOL+$intRow+'='+$ADIPv4AddressCOL+$intRow+')),"same","fail")'}
clear-variable IPADcheck
$hostip = $ping.IPV4Address[0].IPAddressToString
if($hostip -ne $null -and $hostip -eq $thiscomp.IPv4Address){
$IPADcheck = "same"
} else {
$IPADcheck = "fail"
}
if(!$CSVoutput){$excelsheet.Cells.Item($intRow, $checkpingIPagainstAD) = $IPADcheck}
$CSVhash.add($checkpingIPagainstAD,$IPADcheck)
# adds formula to cell to check "ping test IP" and "nslookup" : if ping IP is not blank and they match = same ; else = fail
#if(!$CSVoutput){$excelsheet.Cells.Item($intRow, $checknslookupagainstpingIP) = '=IF(AND('+$pingtestIPCOL+$intRow+'<>"",('+$pingtestIPCOL+$intRow+'='+$allDNSvaluesCOL+$intRow+')),"same","fail")'}
clear-variable NSIPcheck
if($hostip -ne $null -and $nsvalue -ne $null){
if($nsvalue.count -eq $null){$thiscount = 1}else{$thiscount = $nsvalue.count}
for($counter = 0; $counter -lt $thiscount; $counter++){
if($hostip -eq $nsvalue[$counter].IPAddressToString){
$NSIPcheck = "same"
break
} elseif($counter+1 -eq $thiscount){
#none of the DNS IP's match the ping result IP
$NSIPcheck = "missing"
}
}
} else {
$NSIPcheck = "fail"
}
if(!$CSVoutput){
$excelsheet.Cells.Item($intRow, $checknslookupagainstpingIP) = $NSIPcheck
#wmi failed/skipped so set WMI compare check to fail
$excelsheet.Cells.Item($intRow, $checkWMINameagainstADDNS) = "fail"
}
$CSVhash.add($checknslookupagainstpingIP,$NSIPcheck)
$CSVhash.add($checkWMINameagainstADDNS,"fail")
# output csv contents for debugging
$CSVline = ""
for($x = 0; $x -lt $(($CSVheader.toCharArray() | Where-Object {$_ -eq "`t"} | Measure-Object).Count+1); $x++){$CSVline += "`t$($CSVhash.get_item($x))"}
$CSVline = $CSVline.trimstart("`t")
$CSVresults += $CSVline
$intRow++
clear-variable skipADchecks, thiscomp, namecheck, pinglocation, hostip, connectivityresultsvalue, IPADcheck, NSIPcheck
continue
} else {
# ping either failed or set to loopback IP so skip this server
write-host "`nPing failed or set to loopback IP for '$FQDNcomp'" -back red -fore yellow
# output csv contents for debugging
$CSVline = ""
for($x = 0; $x -lt $(($CSVheader.toCharArray() | Where-Object {$_ -eq "`t"} | Measure-Object).Count+1); $x++){$CSVline += "`t$($CSVhash.get_item($x))"}
$CSVline = $CSVline.trimstart("`t")
$CSVresults += $CSVline
$intRow++
clear-variable skipADchecks, thiscomp, namecheck, pinglocation, hostip, connectivityresultsvalue, IPADcheck, NSIPcheck
continue
}
}
#finally, perform formula based checks and populate cell-value check results, the previous method added formulae to the excel cell which worked nicely but prevented automatic saving due to excessive nesting within some of the formulas, the previous method is a one line formula add to a cell and remains here for reference
# adds formula to cell to check "Machine Name" and "WMI-Computer Name" : if they match = same ; else = fail
#if(!$CSVoutput){$excelsheet.Cells.Item($intRow, $checkWMINameagainstADDNS) = '=IF(AND('+$MachineNameCOL+$intRow+'='+$WMIComputerNameCOL+$intRow+','+$MachineNameCOL+$intRow+'='+$nslookupIPnslookupCOL+$intRow+'),"same","fail")'}
clear-variable namecheck
if($thiscomp.name -eq $os.PSComputerName -and $thiscomp.name -eq $nsHOSTname){
$namecheck = "same"
} else {
$namecheck = "fail"
}
if(!$CSVoutput){$excelsheet.Cells.Item($intRow, $checkWMINameagainstADDNS) = $namecheck}
$CSVhash.add($checkWMINameagainstADDNS,$namecheck)
# adds formula to determine location based on IP address, the previous method added formulae to the excel cell which worked nicely but prevented automatic saving due to excessive nesting within some of the formulas, the previous method is two one line formulae in an if else statement to add to a cell and remains here for reference
clear-variable pinglocation, hostip
$hostip = $ping.IPV4Address[0].IPAddressToString
if($scandomain -eq "bottlingcompany.com"){
$pinglocation = ""
switch -wildcard ($hostip){
10.0.0.* {$pinglocation = "TBA"; break}
$null {$pinglocation = "decomm or down"; break}
default {$pinglocation = "new subnet"; break}
}
} elseif($scandomain -eq "lc.local" -or $scandomain -eq "local"){
$pinglocation = ""
switch -wildcard ($hostip){
# 10.99.* {if($strComputer.startswith("LC28NT") -or $strComputer.startswith("LCNT")){$pinglocation = "NT"} else {$pinglocation = "SA"}}
10.0.14.* {$pinglocation = "VIC"; break}
10.90.65.* {$pinglocation = "Xenapp"; break}
10.91.98.* {$pinglocation = "NSW"; break}
192.168.0.* {$pinglocation = "Aurion"; break}
192.168.10.* {$pinglocation = "NSW"; break}
192.168.111.* {$pinglocation = "NSW"; break}
192.168.150.* {$pinglocation = "DMZ"; break}
192.168.19.* {$pinglocation = "One Core"; break}
192.168.191.* {$pinglocation = "VIC"; break}
192.168.195.* {$pinglocation = "QLD"; break}
192.168.2.* {$pinglocation = "Apps"; break}
192.168.20.* {$pinglocation = "Honk Kong"; break}
192.168.42.* {$pinglocation = "Indonesia"; break}
192.168.46.* {$pinglocation = "VIC"; break}
192.168.5.* {$pinglocation = "NSW"; break}
192.168.57.* {$pinglocation = "QLD"; break}
"ping fail" {$pinglocation = "decomm or down"; break}
$null {$pinglocation = "decomm or down"; break}
default {$pinglocation = "new subnet"; break}
}
} elseif ($scandomain -eq "vpl.com.au"){
$pinglocation = ""
switch -wildcard ($hostip){
10.192.56.* {$pinglocation = "ACT"; break}
10.193.* {$pinglocation = "VIC"; break}
10.192.* {$pinglocation = "NSW"; break}
10.92.* {$pinglocation = "NSW"; break}
10.94.* {$pinglocation = "NZ"; break}
10.112.* {$pinglocation = "NSW"; break}
10.113.* {$pinglocation = "VIC"; break}
10.116.* {$pinglocation = "NZ"; break}
10.117.* {$pinglocation = "QLD"; break}
10.118.* {$pinglocation = "WA"; break}
10.119.* {$pinglocation = "SA"; break}
"ping fail" {$pinglocation = "decomm or down"; break}
$null {$pinglocation = "decomm or down"; break}
default {$pinglocation = "new subnet"; break}
}
} else {
$pinglocation = ""
switch -wildcard ($hostip){
10.0.0.* {$pinglocation = "TBA"; break}
"ping fail" {$pinglocation = "decomm or down"; break}
$null {$pinglocation = "decomm or down"; break}
default {$pinglocation = "new subnet"; break}
}
}
if(!$CSVoutput){$excelsheet.Cells.Item($intRow, $Location) = $pinglocation}
$CSVhash.add($Location,$pinglocation)
# adds formula to cell to check "ping test IP", "nslookup" and "WMI-Computer Name" columns
clear-variable connectivityresultsvalue
if($hostip -ne $null -and $nsvalue -ne $null -and $os.PSComputerName -ne $null){
$connectivityresultsvalue = "ok"
} elseif($hostip -ne $null -and $nsvalue -ne $null -and $os.PSComputerName -eq $null){
$connectivityresultsvalue = "WMIFAIL"
} elseif($hostip -ne $null -and $nsvalue -eq $null -and $os.PSComputerName -ne $null){
$connectivityresultsvalue = "DNSFAIL"
} elseif($hostip -ne $null -and $nsvalue -eq $null -and $os.PSComputerName -eq $null){
$connectivityresultsvalue = "DNS&WMIFAIL"
} elseif($hostip -eq $null -and $nsvalue -eq $null){
$connectivityresultsvalue = "PING&DNSFAIL"
} else {
$connectivityresultsvalue = "PINGFAIL"
}
if(!$CSVoutput){$excelsheet.Cells.Item($intRow, $connectivityresults) = $connectivityresultsvalue}
$CSVhash.add($connectivityresults,$connectivityresultsvalue)
# adds formula to cell to check "ping test IP" and "AD-IPv4Address" : if ping IP is not blank and they match = same ; else = fail
#if(!$CSVoutput){$excelsheet.Cells.Item($intRow, $checkpingIPagainstAD) = '=IF(AND('+$pingtestIPCOL+$intRow+'<>"",('+$pingtestIPCOL+$intRow+'='+$ADIPv4AddressCOL+$intRow+')),"same","fail")'}
clear-variable IPADcheck
if($hostip -ne $null -and $hostip -eq $thiscomp.IPv4Address){
$IPADcheck = "same"
} else {
$IPADcheck = "fail"
}
if(!$CSVoutput){$excelsheet.Cells.Item($intRow, $checkpingIPagainstAD) = $IPADcheck}
$CSVhash.add($checkpingIPagainstAD,$IPADcheck)
# adds formula to cell to check "ping test IP" and "nslookup" : if ping IP is not blank and they match = same ; else = fail
#if(!$CSVoutput){$excelsheet.Cells.Item($intRow, $checknslookupagainstpingIP) = '=IF(AND('+$pingtestIPCOL+$intRow+'<>"",('+$pingtestIPCOL+$intRow+'='+$allDNSvaluesCOL+$intRow+')),"same","fail")'}
clear-variable NSIPcheck
if($hostip -ne $null -and $nsvalue -ne $null){
if($nsvalue.count -eq $null){
$thiscount = 1
} else {
$thiscount = $nsvalue.count
}
for($counter = 0; $counter -lt $thiscount; $counter++){
if($hostip -eq $nsvalue[$counter].IPAddressToString){
$NSIPcheck = "same"
break
} elseif($counter+1 -eq $thiscount){
#none of the DNS IP's match the ping result IP
$NSIPcheck = "missing"
}
}
} else {
$NSIPcheck = "fail"
}
if(!$CSVoutput){$excelsheet.Cells.Item($intRow, $checknslookupagainstpingIP) = $NSIPcheck}
$CSVhash.add($checknslookupagainstpingIP,$NSIPcheck)
# output csv contents for debugging and append to CSV results array
$CSVline = ""
for($x = 0; $x -lt $(($CSVheader.toCharArray() | Where-Object {$_ -eq "`t"} | Measure-Object).Count+1); $x++){$CSVline += "`t$($CSVhash.get_item($x))"}
$CSVline = $CSVline.trimstart("`t")
$CSVresults += $CSVline
#move onto the next row
$intRow++
clear-variable skipADchecks, thiscomp, NSIPcheck, CSVhash
}
if(!$CSVoutput){
#set new $excelinfo array to contain very detailed information about each of the cells used in the $excelsheet worksheet, approximately 100 items PER CELL! "$excelinfo | select text" will list each cell's value
$excelinfo = $excelsheet.UsedRange
#enable autofilter and autofit options for entire worksheet, and pipe "true" output to out-null to supress it from being displayed in standard output
$excelinfo.EntireColumn.AutoFilter() | out-null
$excelinfo.EntireColumn.AutoFit() | out-null
}
#Get Current Time For Save File String Format
$temptimedate = get-date -Format yyyy-MMM-dd-Hmm
#write-host "alt path is"$altpath
#save the file to temporary location incase it has been run unattended
if(!(test-path "c:\temp\AD SolarWinds and WMI server audits")){mkdir "c:\temp\AD SolarWinds and WMI server audits"}
if($scandomain -eq "bottlingcompany.com"){
if($altpath){
if(!$CSVoutput){
$automaticsavefile = $altpath+"ADserversCheck-$temptimedate.xlsx"
} else {
$automaticsavefile = $altpath+"ADserversCheck-$temptimedate.csv"
}
}else{
if($serverlistmethod -eq "1"){
if(!$CSVoutput){
$automaticsavefile = "c:\temp\AD SolarWinds and WMI server audits\$temptimedate - ALL bottlingcompany.com AD servers AD ENV and WMI info.xlsx"
$automaticsavefile2 = "c:\temp\AD SolarWinds and WMI server audits\ALL bottlingcompany.com AD servers - AD ENV and WMI info.xlsx"
} else {
$automaticsavefile = "c:\temp\AD SolarWinds and WMI server audits\$temptimedate - ALL bottlingcompany.com AD servers AD ENV and WMI info.csv"
$automaticsavefile2 = "c:\temp\AD SolarWinds and WMI server audits\ALL bottlingcompany.com AD servers - AD ENV and WMI info.csv"
}
} else {
if(!$CSVoutput){
$automaticsavefile = "c:\temp\AD SolarWinds and WMI server audits\$temptimedate - SELECTED bottlingcompany.com AD servers AD ENV and WMI info.xlsx"
$automaticsavefile2 = "c:\temp\AD SolarWinds and WMI server audits\SELECTED bottlingcompany.com AD servers - AD ENV and WMI info.xlsx"
} else {
$automaticsavefile = "c:\temp\AD SolarWinds and WMI server audits\$temptimedate - SELECTED bottlingcompany.com AD servers AD ENV and WMI info.csv"
$automaticsavefile2 = "c:\temp\AD SolarWinds and WMI server audits\SELECTED bottlingcompany.com AD servers - AD ENV and WMI info.csv"
}
}
}
} else {
if($altpath){
if(!$CSVoutput){
$automaticsavefile = $altpath+"ADserversCheck-"+$global:thisdomainROOT+"-$temptimedate.xlsx"
} else {
$automaticsavefile = $altpath+"ADserversCheck-"+$global:thisdomainROOT+"-$temptimedate.csv"
}
}else{
if($serverlistmethod -eq "1"){
if(!$CSVoutput){
$automaticsavefile = "c:\temp\AD SolarWinds and WMI server audits\$temptimedate - ALL "+$global:thisdomainROOT+" servers AD ENV and WMI info.xlsx"
$automaticsavefile2 = "c:\temp\AD SolarWinds and WMI server audits\ALL "+$global:thisdomainROOT+" servers - AD ENV and WMI info.xlsx"
} else {
$automaticsavefile = "c:\temp\AD SolarWinds and WMI server audits\$temptimedate - ALL "+$global:thisdomainROOT+" servers AD ENV and WMI info.csv"
$automaticsavefile2 = "c:\temp\AD SolarWinds and WMI server audits\ALL "+$global:thisdomainROOT+" servers - AD ENV and WMI info.csv"
}
} else {
if(!$CSVoutput){
$automaticsavefile = "c:\temp\AD SolarWinds and WMI server audits\$temptimedate - SELECTED "+$global:thisdomainROOT+" servers AD ENV and WMI info.xlsx"
$automaticsavefile2 = "c:\temp\AD SolarWinds and WMI server audits\SELECTED "+$global:thisdomainROOT+" servers - AD ENV and WMI info.xlsx"
} else {
$automaticsavefile = "c:\temp\AD SolarWinds and WMI server audits\$temptimedate - SELECTED "+$global:thisdomainROOT+" servers AD ENV and WMI info.csv"
$automaticsavefile2 = "c:\temp\AD SolarWinds and WMI server audits\SELECTED "+$global:thisdomainROOT+" servers - AD ENV and WMI info.csv"
}
}
}
}
#Get End Time
$endDTM = (Get-Date)
#Echo Time elapsed
Write-Host "`n`nDone!!! Program execution completed. Program Run Time: $(($endDTM-$startDTM).totalseconds) seconds ($(($endDTM-$startDTM).totalminutes) minutes)" -back black -fore green
if(!$CSVoutput){
$xlFixedFormat = [Microsoft.Office.Interop.Excel.XlFileFormat]::xlWorkbookDefault
$excelbook.SaveAs($automaticsavefile,$xlFixedFormat)
$excelbook.close()
}
if(!$CSVoutput){
#now that program has finished, make excel visible if its not already and quit the application
if($scandomain -eq "bottlingcompany.com"){
$thisexcelobj.visible = $True
[void]$thisexcelobj.Quit()
# the following line outputs a "2" if not output to null
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($thisexcelobj) | out-null
} else {
$myotherexcelobj.visible = $True
[void]$myotherexcelobj.Quit()
# the following line outputs a "2" if not output to null
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($myotherexcelobj) | out-null
}
#use the kill alias (or stop-process command) to kill that PID after you've saved and closed your excel file, and for additional output and checking, use passthru to return kill information to a variable and check whether a result was passed thru or not
if(!$global:thisexcel){
$killresult = stop-process -name "excel" -force -passthru
} else {
$killresult = stop-process $global:thisexcel -force -passthru
}
if($killresult){Write-Host "`nExcel.exe instance PID"$killresult.id"successfully terminated...`n" -back black -fore green}else{Write-Host "`nSomething went wrong, please verify Excel.exe instance PID"$killresult.id" was successfully terminated...`n" -back black -fore red}
} else {
# output the CSV file
$CSVheader | out-file $automaticsavefile
# based on the dynamic header length, write out the contents of the hash array to the csv output file
$CSVresults | out-file $automaticsavefile -append
}
#the following line if uncommented, copies the latest result file over the top of a statically named file, so up to date data can be accessed via a hyperlink for instance
if(!($altpath) -and $serverlistmethod -eq "1" -and !($skipWMI) -and !($nodomain)){
copy-item $automaticsavefile -destination $automaticsavefile2 -force
}
#print file save details to screen
if($altpath){
if(!$CSVoutput){
Write-Host "`n`nXLS File Saved to alternate path '$savepath' directory`n`n" -back black -fore white
} else {
Write-Host "`n`nCSV File Saved to alternate path '$savepath' directory`n`n" -back black -fore white
}
}else{
if(!$CSVoutput){
Write-Host "`n`nXLS File saved: & '$automaticsavefile'`n`n" -back black -fore white
} else {
Write-Host "`n`nCSV File saved: & '$automaticsavefile'`n`n" -back black -fore white
}
}
|
